How to force postgres to return 0 even if there are no rows matching query, using coalesce, group by and join

Question

I've been trying hopelessly to get the following SQL statement to return the query results and default to 0 if there are no rows matching the query.

This is the intended result:

  vol  | year 
-------+------
   0   | 2018

Instead I get:

 vol | year 
-----+------
(0 rows)

Here is the sql statement:

select coalesce(vol,0) as vol, year
from (select sum(vol) as vol, year
      from schema.fact_data
         join schema.period_data
            on schema.fact_data.period_tag = schema.period_data.tag 
         join schema.product_data
            on schema.fact_data.product_tag = 
schema.product_data.tag
         join schema.market_data
            on schema.fact_data.market_tag = schema.market_data.tag
      where "retailer"='MadeUpRetailer'
        and "product_tag"='FakeProductTag'
        and "year"='2018' group by year
     ) as DerivedTable;

I know the query works because it returns data when there is data. Just doesn't default to 0 as intended...

Any help in finding why this is the case would be much appreciated!

Answer 1

Using your subquery DerivedTable, you could write:

SELECT coalesce(DerivedTable.vol, 0) AS vol,
       y.year
FROM (VALUES ('2018'::text)) AS y(year)
   LEFT JOIN (SELECT ...) AS DerivedTable
      ON DerivedTable.year = y.year;