Using aggregate functions in SQL query

Question

I have my Table structure like this ::

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.

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.

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).

How do I this? Do I have to use a nested WHERE IN clause ? Thanks.

Biju P · Accepted Answer · 2012-03-30 06:51:34Z

3

SELECT  MIN(F_Name) Employee_Name ,
        COUNT(1) Activities_Assigned ,
        AVG(Percent_Complete) Load_Value
FROM    ATT_Table a
        INNER JOIN Employee_Table e ON a.Assigned_To_ID = e.Emp_ID
GROUP BY e.Emp_ID

answered Mar 30, 2012 at 6:51

Biju P

1083 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dmitri · Accepted Answer · 2012-03-30 06:35:20Z

2

I may be missing some nuance, but it sounds like you can just: join the tables, group by employee, COUNT and AVG for the load.

answered Mar 30, 2012 at 6:35

Dmitri

9,1675 gold badges39 silver badges43 bronze badges

Comments

Vikram · Accepted Answer · 2012-03-30 06:44:43Z

1

try the following:

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

answered Mar 30, 2012 at 6:44

Vikram

8,32335 silver badges47 bronze badges

Comments

LasseValentini · Accepted Answer · 2012-03-30 06:46:35Z

0

As Dmitri said, something like

SELECT Employee_Table.F_Name, COUNT(*) AS activities, AVG(Percent_Complete) AS load
FROM ATT_Table JOIN Employee_Table ON ATT_Table.Assigned_to_ID = Employee_Table.Emp_ID
WHERE Employee_Table.Emp_ID = 42
GROUP BY Employee_Table.Emp_ID

answered Mar 30, 2012 at 6:46

LasseValentini

2661 silver badge3 bronze badges

Collectives™ on Stack Overflow

Using aggregate functions in SQL query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related