在SQL查询中使用聚合函数 [英] Using aggregate functions in SQL query
问题描述
我的表格结构如下:
ATT_Table : Fields - Act_ID, Assigned_To_ID, Percent_Complete(Integer value)
Act_ID is primary key, Assigned_To_ID is referenced to Emp_ID in Employee_Table.
Employee_Table : Fields - Emp_ID, F_Name.
Emp_ID is primary key.
现在在特定时间点,可以将1个或多个活动分配给同一个人.我的目标是编写查询以计算人员的负担.我想计算分配给特定人的活动数量(可以大于1),然后取其percent_Complete
的平均值.
Now at a particular point in time, 1 or more activities can be assigned to same person. My goal is write a query to calculate a person's load. I want to count the number of activities assigned to a particular person (can be more than 1) then take the average of their percent_Complete
.
例如,如果给人员A分配了A1,A2,A3(Act_ID).然后对应(Percent_Complete值加法)/3.基本上是平均水平.在我的最终查询结果中,我想要:
For example if person A is assigned A1, A2, A3(Act_ID). Then corresponding (Percent_Complete values addition)/3. Basically an average. In my final query result I want:
Name, Number of activities assigned(Count), load value(Avg).
我该怎么办?我是否必须使用嵌套的WHERE IN
子句?谢谢.
How do I this? Do I have to use a nested WHERE IN
clause ? Thanks.
推荐答案
尝试以下操作:
select Emp_ID, F_Name, count(Act_ID), avg(Percent_Complete)
from ATT_Table, Employee_Table where ATT_Table.Assigned_To_ID = Employee_Table.Emp_ID
group by Emp_ID, F_Name
这篇关于在SQL查询中使用聚合函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!