muellerfan
asked on
Multiple aggregate functions
I am trying to write a clean, simple query to get some aggregate data. For each person, I want to know the sum of their allocated hours and the sum of their logged hours. They can be assigned multiple tasks and they can have multiple hours entries for each task.
The tables are much more complex than this, but I think this is enough to ask my question:
Person
----------
Person_pri
Name
Task
------
Task_pri
AllocatedHours
TaskAssigned
--------
Task_pri
Person_pri
Hours
---------
Task_pri
Person_pri
Hours
Right now, I have this
SELECT Person.name, sum(Task.AllocatedHours), sum(Hours.Hours)
FROM Person
INNER JOIN TaskAssigned ON Person.Person_pri = TaskAssigned.Person_pri
INNER JOIN Task ON TaskAssigned.Task_pri = Task.Task_pri
INNER JOIN Hours ON Task.Task_pri = Hours.Task_pri AND Person.Person_pri = Hours.Person_pri
GROUP BY Person.name
Now, the obvious problem here is that if there are multiple hours records per task, the AllocatedHours for each task will be added multiple times. So in this case, the sum(Hours.Hours) comes out right, but the sum(Task.AllocatedHours) is huge. If I remove the join on Hours, that number is right, but of course I lose the sum on the Hours.Hours.
Is there any way to get all this into one query?
Thanks,
Christie
The tables are much more complex than this, but I think this is enough to ask my question:
Person
----------
Person_pri
Name
Task
------
Task_pri
AllocatedHours
TaskAssigned
--------
Task_pri
Person_pri
Hours
---------
Task_pri
Person_pri
Hours
Right now, I have this
SELECT Person.name, sum(Task.AllocatedHours), sum(Hours.Hours)
FROM Person
INNER JOIN TaskAssigned ON Person.Person_pri = TaskAssigned.Person_pri
INNER JOIN Task ON TaskAssigned.Task_pri = Task.Task_pri
INNER JOIN Hours ON Task.Task_pri = Hours.Task_pri AND Person.Person_pri = Hours.Person_pri
GROUP BY Person.name
Now, the obvious problem here is that if there are multiple hours records per task, the AllocatedHours for each task will be added multiple times. So in this case, the sum(Hours.Hours) comes out right, but the sum(Task.AllocatedHours) is huge. If I remove the join on Hours, that number is right, but of course I lose the sum on the Hours.Hours.
Is there any way to get all this into one query?
Thanks,
Christie
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just some syntax correction for Lowfatspread's query to make it work properly. :)
SELECT P.name, sum(T.AllocatedHours), sum(H.Hours)
FROM Person as P
Left Outer Join (select person_pri,sum(allocatedho
from TaskAssigned as ta
Inner Join task as t
on ta.task_pri=t.task_pri
group by person_pri) as T
ON P.Person_pri = T.Person_pri
Left Outer Join (select person_pri,sum(hours) as Hours
from Hours
group by person_pri) as H
ON P.Person_pri = H.Person_pri
GROUP BY P.name
SELECT P.name, sum(T.AllocatedHours), sum(H.Hours)
FROM Person as P
Left Outer Join (select person_pri,sum(allocatedho
from TaskAssigned as ta
Inner Join task as t
on ta.task_pri=t.task_pri
group by person_pri) as T
ON P.Person_pri = T.Person_pri
Left Outer Join (select person_pri,sum(hours) as Hours
from Hours
group by person_pri) as H
ON P.Person_pri = H.Person_pri
GROUP BY P.name
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the suggestions. I will follow this concept. The only thing is that I need to join the Hours table with the Task table. I don't want all hours for that person, but hours that are associated with a Task.
I will play around with it on my own, but if you have suggestions, I would appreciate it.
Thanks again.
I will play around with it on my own, but if you have suggestions, I would appreciate it.
Thanks again.
ASKER
I ended up just making larger subqueries. Thanks again for your help!
then proably
from person as p
inner join task_assigned as ta
on p.person_pri=ta.person_pri
Left outer join ( select person_pri,task_pri,sum(ho
) as h
on p.person_pri=h.person_pri
and ta.task_pri=h.task_pri
left outer join (select ...)
.
from person as p
inner join task_assigned as ta
on p.person_pri=ta.person_pri
Left outer join ( select person_pri,task_pri,sum(ho
) as h
on p.person_pri=h.person_pri
and ta.task_pri=h.task_pri
left outer join (select ...)
.
SELECT a.name, b.sum_alloc, isnull(c.sum_hours, 0) as sum_hours
FROM Person a
JOIN (SELECT ax.Person_pri, SUM(bx.AllocatedHours) as sum_alloc FROM TaskAssigned ax JOIN Task bx on ax.Task_pri = bx.Task_pri GROUP BY ax.Person_pri) b on a.Person_pri = b.Person_pri
LEFT OUTER JOIN (SELECT ax.Person_pri, SUM(ax.Hours) as sum_hours FROM Hours) c on a.Person_pri = c.Person_pri