CODEWITHSUNDEEP
sqlpostgresql
craigcaulfield
craigcaulfield

Reputation: 3538

Making PostgreSQL return values if a record is not found

I have a PostgreSQL 9.6 table that looks like this:

range (string)          count           suburb
--------------          ------          ------
< 200,000                  1            NEDLANDS
400,000 to 599,000         4            NEDLANDS
600,000 to 799,000         5            NEDLANDS
> 2                        1            NEDLANDS

If a range's count is 0, there's no record in the table, but I want my query to return every range and report 0 where no record exists. So, for the above table, the desired result set would be:

range (string)          count
--------------          ------
<200,000                   1
200,000 to 400,000         0
400,000 to 599,000         4
600,000 to 799,000         5
800,000 to 1m              0
1m > 2m                    0
> 2                        1

I thought I could union the query that finds the records with a query that doesn't find the records, but my query is still does not find 0-count records:

select range, count 
        from sales 
        where suburb = 'NEDLANDS' 

union all

select range, 0 as count from sales 
where not exists (select range, count 
                  from sales 
                  where suburb = 'NEDLANDS' );

Is there something I'm missing?

Upvotes: 1

Views: 6239

Answers (3)

GregHNZ
GregHNZ

Reputation: 8989

The best way to do it would be to have a table with the list of valid ranges, for example valid_ranges - this will have a row for each of the valid range headings.

Then you can go

SELECT vr.range, s.count
FROM valid_ranges vr 
LEFT JOIN sales s ON vr.range = s.range
WHERE s.suburb = 'NEDLANDS' OR s.suburb IS NULL;

This last line is a bit peculiar. The 'IS NULL' clause is there for the missing rows, but for this to work properly, suburb must not be a nullable column.

If suburb IS nullable, you can restructure:

SELECT vr.range, n.count
FROM valid_ranges vr
LEFT JOIN (SELECT range, count FROM sales
              WHERE suburb = 'NEDLANDS') n
    ON vr.range = n.range;

If you can't create a table of valid ranges, you can get ALL of the ranges from the sales table instead. But this has the risk that if there is NO suburb with that range, then it simply won't show in the list:

SELECT vr.range, n.count
FROM (SELECT DISTINCT range FROM sales) vr
LEFT JOIN (SELECT range, count FROM sales
              WHERE suburb = 'NEDLANDS') n
    ON vr.range = n.range;

These all will have a NULL in the count if there's no row. If you really need that zero, you use COALESCE:

SELECT vr.range, COALESCE (n.count, 0)
FROM valid_ranges vr
LEFT JOIN (SELECT range, count FROM sales
              WHERE suburb = 'REDLANDS') n
    ON vr.range = n.range;

(Edit: Corrected the COALESCE() call as @craigcaulfield pointed out)

Upvotes: 3

Paul Maxwell
Paul Maxwell

Reputation: 35613

Form a small CTE (common table expression) of all range values (or that could be altered to a subquery), then left join your existing query to that

WITH MyRanges (range) AS (
    select '<200,0001' as range union all
    select '200,000 to 400,000' union all
    select '400,000 to 599,000' union all
    select '600,000 to 799,000' union all
    select '800,000 to 1m' union all
    select '1m > 2m' union all
    select '> 2'
    )
select
       m.range
     , coalesce(s.count,0)
from MyRanges m
left join (
        select range, count 
        from sales 
        where suburb = 'NEDLANDS' 
    ) s ON m.range = s.range
;

You could extend the same logic of the CTE to hold the boundary values for the ranges too, which might make the balance of your query simpler - but it isn't clear how you have prepared the counts into ranges.

Upvotes: 0

psrpsrpsr
psrpsrpsr

Reputation: 457

You could create a table with all of the desired values, and then do a left join to the values you return.

dummy_table:

dummy_field

<200,000
200,000 to 400,000
400,000 to 599,000
600,000 to 799,000
800,000 to 1m 
1m > 2m 
> 2       


select *
from dummy_table
left join (select * from your subquery) on dummy_field = your_subquery_dummy_field

Upvotes: 0

Related Questions