Need Help with Summing Query

[Stang70Fastback]

Need Help with Summing Query (SOLVED!)

Hello again! Another question, since you were so helpful with the last one:

I have four columns: Employee, Discipline, AvailableHours, TotalHours.

I want to summarize these by Discipline, so that for a list of employees, I wind up with a list of the various disciplines, with the TotalHours per discipline. HOWEVER, the available hours should be summed, but only adding ONE for each employee. It's hard to explain, so hopefully the example below makes more sense:

DATA

John - Architect - 40 - 10
Mary - Architect - 40 - 10
John - Architect - 40 - 20
Mary - Architect - 40 - 30
John - Architect - 40 - 10
Bill - Plumber - 40 - 10

DESIRED RESULT

Architect - 80 - 80
Plumber - 40 - 10

Notice that it is summing the last column, but only summing the third column for ONE entry for each unique individual. That's because each record contains the working hours the employee can work (40 hours), along with the hours they've been assigned for THAT record. So I want to sum all of their assigned hours, but obviously the hours they CAN work stays the same. They don't work a 40 hour work week for EACH job they are assigned to, if that makes sense.

My current query sums BOTH columns completely, so it falsely states that there are 200 available working hours between the two architectural employees, when there are only 80 hours between the two. My current query is:

SELECT Discipline, Sum(ManpowerData.AvailableHours) As AvailableHours, Sum(ManpowerData.TotalHours) As TotalHours
FROM ManpowerData
GROUP BY Discipline;

So I'm wondering if there is any way to sum the AvailableHours column but tell the system to only count each employee once.

 

[CJ_London]

if the 40 column is always 40, you can average it rather than sum it

 

[Stang70Fastback]

I actually did do exactly that in another query where things happened a bit differently because the numbers were grouped by employee. In this case, that isn't possible because some of the employees are part-time employees, so the number fluctuates.

I actually resolved this issue, but forgot to follow-up on this thread. I ended up splitting this into two separate queries, and then LEFT OUTER JOINing them together to get the data I wanted. So it's all solved!