blacksaibot blacksaibot - 2 months ago 13
SQL Question

SUM using CASE WHEN SELECT

How can I re-write this query so that it doesn't trigger the following error:

"Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

SELECT
employees.loc_id
,SUM(CASE
WHEN
pos IN (SELECT DISTINCT pos FROM lookup WHERE lbl='Staff')
AND
loc IN (SELECT DISTINCT loc FROM lookup WHERE lbl='Staff')
THEN 1
ELSE 0
END) AS 'Staff Site 1'
,SUM(CASE
WHEN
pos IN (SELECT DISTINCT pos FROM table WHERE lbl='Staff')
AND
loc IN (SELECT DISTINCT loc FROM table WHERE lbl='Staff')
THEN 1
ELSE 0
END) AS 'Staff Site 2'

FROM table GROUP BY table.key_id ORDER BY key_id


So apparently I cannot put a SELECT statement within SUM? How can I make sure I'm only summing for the cases described in the above code?

pos determines the type of position. loc determines at what site (location). The lookup table is structured like this:

pos | loc | lbl
123 | XYZ | Staff Site 1
456 | XYZ | Staff Site 1
987 | XYZ | Supervisor Site 1
123 | ABC | Staff Site 2
123 | JKL | Staff Site 3
123 | OPQ | Staff Site 4
456 | OPQ | Staff Site 4
345 | OPQ | Staff Site 4


This lookup table simply determines the label associated with a position at a specific location.

There is an employee table and each employee is assigned a pos#.

So I wanted my query to select all the loc_id (location id) from the employee table, and for each loc_id, sum each respective entry in the lookup table

So if I had 30 employees with loc_id=XYZ with pos=123 and 20 employees with loc_id=XYZ with pos=456, the output would be

loc_id | Staff Site 1
XYZ | 50


Thanks in advance, and I hope this was not too confusing...

Answer

Without going into techniques to get around being able to SELECT inside an aggregate function. Let's step back and rethink your query all together. You are trying to solve it by aggregating the Lookup table but the aggregation is really on the Employee table with a join from the lookup table.

It also appears that you are trying to do conditional aggregation to PIVOT your result. But in either event if you do the following you will get the result you specify if you do the following:

SELECT
    l.loc
    ,COUNT(DISTINCT e.Id) as EmployeesAtStaffSite1
FROM
    Lookup l
    INNER JOIN Employees e
    ON e.loc = l.loc
    AND e.pos = l.pos
WHERE
    l.lbl = 'Staff Site 1'
GROUP BY
    l.loc

Also note that if you change INNER JOIN to LEFT JOIN you can get 0 count for any locations that are staff site 1 that there are no employees assigned.

Comments