SUM Function with WHERE clause for each sum?

user_9083735 0
Viewed 59

I have two tables: Staff and Hours.

Staff(200+ rows) with:

Staff_ID, Name, Position

each staff member only appears once in this table

Hours (1000+ rows) with:

Staff_ID, Hours, Amount, BillableFlag, Office, DatePeriod

each staff member appears many times in this table

I need a query that pulls in all Staff from the Staff table with position = 'Assistant' and the Hours and Amount columns from the Hours table summed twice each with different logic for each sum.

Something like this ( it's a query with some plain english to help with the explanation ):

select s.name,
sum(h.hours) as 'Hours_Not_Billable' --[NEED IT TO SUM ONLY HOURS WHERE h.BILLABLEFLAG <> 'Y', h.office = '2', h.period = 'Q3' AND ONLY DO IT FOR EACH STAFF MEMBER],
sum(h.amount) as 'Amount_Not_Billable' --[NEED IT TO SUM ONLY AMOUNT WHERE h.BILLABLEFLAG <> 'Y', h.office = '2', h.period = 'Q3' AND ONLY DO IT FOR EACH STAFF MEMBER],
sum(h.hours) as 'Hours_Billable' --[NEED IT TO SUM ONLY HOURS WHERE h.BILLABLEFLAG = 'Y', h.office = '2', h.period = 'Q3' AND ONLY DO IT FOR EACH STAFF MEMBER],
sum(h.amount) as 'Amount_Billable --[NEED IT TO SUM ONLY HOURS WHERE h.BILLABLEFLAG = 'Y', h.office = '2', h.period = 'Q3' AND ONLY DO IT FOR EACH STAFF MEMBER]'
from Staff S 
left join Hours H on S.Staff_ID = H.Staff_ID

where s.position = 'Assistant'
group by s.name

Help much appreciated!

Thank you

pivot sum tsql sql-server sql
2 Answers

Use conditional aggregation. The idea is to put a case expression inside the sum(), that filters out with values are taken into account.

Your pseudo-code would translate as:

select 
    s.name,
    sum(case when h.billableflag <> 'Y' then h.hours  else 0 end) hours_not_billable,
    sum(case when h.billableflag <> 'Y' then h.amount else 0 end) amount_not_billable,
    sum(case when h.billableflag =  'Y' then h.hours  else 0 end) hours_billable,
    sum(case when h.billableflag =  'Y' then h.amount else 0 end) amount_billable
from staff s 
left join hours h 
    on s.staff_id = h.staff_id and h.office = 2 and h.period = 'Q3'
where s.position = 'Assistant'
group by s.name

Note: most of the conditions are common to all expressions, so I moved them to the on clause of the left join.

user_106767160

You can use CASE WHEN within SUM() aggregation function as follows

select 
s.name,
sum(case when h.BILLABLEFLAG <> 'Y' and h.office = '2' and h.DatePeriod = 'Q3' then h.hours else null end) as 'Hours_Not_Billable', 
sum(case when h.BILLABLEFLAG <> 'Y' and h.office = '2' and h.DatePeriod = 'Q3' then h.amount else null end) as 'Amount_Not_Billable', 
sum(case when h.BILLABLEFLAG = 'Y' and h.office = '2' and h.DatePeriod = 'Q3' then h.hours else null end) as 'Hours_Billable',
sum(case when h.BILLABLEFLAG = 'Y' and h.office = '2' and h.DatePeriod = 'Q3' then h.amount else null end ) as 'Amount_Billable'
from Staff S 
left join Hours H on S.Staff_ID = H.Staff_ID
where s.position = 'Assistant'
group by s.name
user_8329910
Related