Re: How to get non-existant values as NULL or 0?

On Sat, Jun 29, 2019 at 9:46 AM Pól Ua Laoínecháin <linehanp(at)tcd(dot)ie> wrote:

> 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.