CODEWITHSUNDEEP
sqlpostgresqldatejoin
Umer
Umer

Reputation: 250

Postgres: Return zero as default for rows where there is no matach

I am trying to get all the paid contracts from my contracts table and group them by month. I can get the data but for months where there is no new paid contract I want to get a zero instead of missing month. I have tried coalesce and generate_series but I cannot seem to get the missing row.

Here is my query:

 with months as (   
       select generate_series(
        '2019-01-01', current_date, interval '1 month'   
) as series )
    select date(months.series) as day, SUM(contracts.price) from months 
left JOIN contracts on date(date_trunc('month', contracts.to)) = months.series 
where contracts.tier='paid' and contracts.trial=false and (contracts.to is not NULL)  group by day;

I want the results to look like:

|Contract Value| Month|
| 20           | 01-2020|
| 10           | 02-2020|
| 0            | 03-2020|

I can get the rows where there is a contract but cannot get the zero row.

Postgres Version 10.9

Upvotes: 1

Views: 282

Answers (2)

GMB
GMB

Reputation: 222582

I think that you want:

with months as (   
       select generate_series('2019-01-01', current_date, interval '1 month'   ) as series 
)
select m.series as day, coalesce(sum(c.price), 0) sum_price
from months m
left join contracts c
    on  c.to >= m.series 
    and c.to <  m.series  + interval '1' month
    and co.tier = 'paid' 
    and not c.trial
group by m.series;

That is:

  • you want the condition on the left joined table in the on clause of the join rather than in the where clause, otherwise they become mandatory, and evict rows where the left join came back empty

  • the filter on the date can be optimized to avoid using date functions; this makes the query SARGeable, ie the database may take advantage of an index on the date column

  • table aliases make the query easier to read and write

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You need to move conditions to the on clause:

with months as (   
     select generate_series( '2019-01-01'::date, current_date, interval '1 month') as series
    )
select dm.series as day, coalesce(sum(c.price), 0)
from months m left join
     contracts c
     on c.to >= m.series and
        c.to < m.series + interval '1 month' and
        c.tier = 'paid' and
        c.trial = false
group by day;

Note some changes to the query:

  • The conditions on c that were in the where clause are in the on clause.
  • The date comparison uses simple data comparisons, rather than truncating to the month. This helps the optimizer and makes it easier to use an index.
  • Table aliases make the query easier to write and to read.
  • There is no need to convert day to a date. It already is.
  • to is a bad choice for a column name because it is reserved. However, I did not change it.

Upvotes: 1

Related Questions