Making PostgreSQL return values if a record is not found

Question

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?

GregHNZ · Accepted Answer · 2017-10-31 04:41:08Z

3

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)

edited Oct 31, 2017 at 4:41

answered Oct 31, 2017 at 0:43

GregHNZ

9,0581 gold badge32 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

craigcaulfield Over a year ago

I used the last example with COALESCE (n.count,0) and it worked perfectly.

psrpsrpsr · Accepted Answer · 2017-10-31 00:36:47Z

0

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

edited Oct 31, 2017 at 0:36

answered Oct 31, 2017 at 0:29

psrpsrpsr

4571 gold badge5 silver badges13 bronze badges

1 Comment

craigcaulfield Over a year ago

I have read-only access to the database so I'm constrained in what I can do.

Paul Maxwell · Accepted Answer · 2017-10-31 01:04:16Z

0

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.

edited Oct 31, 2017 at 1:04

answered Oct 31, 2017 at 0:59

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

5 Comments

craigcaulfield Over a year ago

The ranges are dynamic and could change at any time so I wanted to avoid referring to them explicitly in my query.

Paul Maxwell Over a year ago

How do you define the dynamic ranges now? By changes in sql?

craigcaulfield Over a year ago

That's right, someone else may update the ranges with an SQL update, but I won't know until after the fact.

Paul Maxwell Over a year ago

Did you not say you only had read access? I took that very literally. You have an accepted answer requiring a table, and if they exist in a table then that's good. Sorry my answer didn't suit but perhaps someone else with read only access will find it helpful.

craigcaulfield Over a year ago

I was able to use the final option in the accepted answer which doesn't require a new table.

Collectives™ on Stack Overflow

Making PostgreSQL return values if a record is not found

3 Answers 3

1 Comment

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related