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."
pos IN (SELECT DISTINCT pos FROM lookup WHERE lbl='Staff')
loc IN (SELECT DISTINCT loc FROM lookup WHERE lbl='Staff')
END) AS 'Staff Site 1'
pos IN (SELECT DISTINCT pos FROM table WHERE lbl='Staff')
loc IN (SELECT DISTINCT loc FROM table WHERE lbl='Staff')
END) AS 'Staff Site 2'
FROM table GROUP BY table.key_id ORDER BY key_id
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
loc_id | Staff Site 1
XYZ | 50
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.