I'm in a bit of a quandary. I'm trying to figure out how to have
non-existent values appear as NULL (or using COALESCE, as 0).
See if this helps.
WITH job (j) AS ( VALUES ('Unknown'), ('Clean'), ('Cook') ),
location (loc) AS ( VALUES ('Here'), ('There') ),
data (j, loc, cnt) AS ( VALUES ('Clean', 'Here', 1) )
SELECT j, loc, COALESCE(cnt, 0) AS effective_count
FROM (job CROSS JOIN location) AS master_list
LEFT JOIN data USING (j, loc)
Basically you need to define everything that you care about using your lookup tables (cross join is needed here to combine multiple tables) then left join to that the data that you have that also has the same lookup keys. If data is missing the left join produces a null which you can then coalesce. Performing aggregation on the result is then a simple matter of moving the detail query into a subquery, though you can also aggregate the actual detail first and join that, or probably less efficiently, move the detail aggregate into the select-list of the query:
SELECT j, loc, (SELECT sum(cnt) FROM data WHERE data.j = master_list.j AND data.loc = master_list.loc) AS effective_count
David J.
