Multiple aggregate functions
Posted on 2007-04-02
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:
Right now, I have this
SELECT Person.name, sum(Task.AllocatedHours), sum(Hours.Hours)
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?