CODEWITHSUNDEEP
sqlpostgresqlleft-join
Chrisvdberge
Chrisvdberge

Reputation: 1956

Select query to show missing values per day as 0

I have one table with product id's and another with daily data for these products. When there is no data I want to return 0 instead of no value at all. I tried using a left join but this doesn't give the resultset I want since the table I can join on has product id's only, not per day.

I guess I have to generate a table first with all products for all dates and then join my data on that. Not sure how to construct this though.

Example of the products table:

id - product
1 - test1
2 - test2
3 - test3

example of the daily data:

product - date - value
test1 - 2020-01-01 - 10
test2 - 2020-01-01 - 8
test3 - 2020-01-01 - 9
test1 - 2020-01-02 - 9
test3 - 2020-01-02 - 10
test2 - 2020-01-03 - 6
test3 - 2020-01-03 - 5

Result I'm looking for: 

product - date - value
test1 - 2020-01-01 - 10
test2 - 2020-01-01 - 8
test3 - 2020-01-01 - 9
test1 - 2020-01-02 - 9
test2 - 2020-01-02 - 0
test3 - 2020-01-02 - 10
test1 - 2020-01-03 - 0
test2 - 2020-01-03 - 6
test3 - 2020-01-03 - 5

Upvotes: 1

Views: 509

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You can cross join to get the rows and then left join to bring in the data you want:

select p.id, c.date, coalesce(d.value, 0) as value
from products p cross join
     (select distinct date from daily) c left join
     daily d
     on d.product = p.id and d.date = c.date;

If there are dates that are not in the table, you can generate the dates using generate_series():

select p.id, c.date, coalesce(d.value, 0) as value
from products p cross join
     (select generate_series(min(d.date), max(d.date), interval '1 day') as date
      from daily
     ) c left join
     daily d
     on d.product = p.id and d.date = c.date;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

Yuo could use a subquery and cross join for find the missing combination

select p.product, d.date, ifnull(value,0)
from  (
  select distinct p.produtc, d.date 
  from product 
  cross join date 
) t 
inner join product p on t.product = d.product
inner  join  data d on t.date = d.date

Upvotes: 1

Related Questions