Aggregate and group with case when statement

Question

I am trying to write a query against a table that contains daily payroll data for employees at different locations. I need to be able to get a count of employee ids that have greater than 30 paid hours in a week and a count of employee ids that have less than 20 for the same week. I've tried this with no luck:

select
[WEEKENDING],
[LOCATION],
sum(case when [PAID_HOURS] > 30 then 1 else 0 end) as [COUNT_OVER_30],
sum(case when [PAID_HOURS] < 20 then 1 else 0 end) as [COUNT_UNDER_20]
from mydb.payroll
group by [WEEKENDING],[LOCATION]

This is the source table:

Here is the desired result:

Thank you so much in advance!!!

Answer 1

You seem to have the right idea, but maybe in the wrong order. First group by EMPID, WEEKENDING, FACILITY to sum the number of hours per employee, then count the EMPIDs for each case. So, something like:

SELECT WEEKENDING, FACILITY,
    SUM( CASE WHEN WORKED_HOURS > 30 THEN 1 ELSE 0 END ) AS WH30,
    SUM( CASE WHEN WORKED_HOURS < 20 THEN 1 ELSE 0 END ) AS WH20,
SELECT W, EMPID, FACILITY, SUM(WORKED_HOURS) AS WH
FROM (
    SELECT EMPID, WEEKENDING, FACILITY, 
        SUM(WORKED_HOURS) AS WORKED_HOURS
    FROM Wherever
    GROUP BY EMPID, WEEKENDING, FACILITY
) T
GROUP BY W, FACILITY