Extract date-by-date values also when no data

Question

I have this table

records
- id
- date
- typology

I want to extract a day-by-day count of records with typology = 999. I know there's at least 1 record for each day, but most of days I don't have a record with typology 999.

I tried something like

select count(*) from records where typology = 999 and date > '2017-01-01' group by date order by date asc;

hoping to get something like

285 | 2017-01-06 |
307 | 2017-01-07 |
  0 | 2017-01-08 |
316 | 2017-01-09 |

but I don't. I get

285 | 2017-01-06 |
307 | 2017-01-07 |
316 | 2017-01-09 |

and that's right. Since there are no records with typology 999 on the 8th of january, I don't get that line. But I'd like to. I don't have to "invent" the 2017-01-08 date, there are for sure records with that date (but different typologies).

I tried a bit with coalesce and subqueries but coudn't get much out of it, mostly because I have two fields and coalesce seems to me to work fine just with one.

I tried with a CASE as well

SELECT CASE c WHEN NULL THEN 0 ELSE c END
FROM (
select count(*) as c from records where typology = 892 and date > '2017-01-01' group by date order by date asc
) as s;

didn't work either. I'm pretty sure it's quite a messed-up query.

Do you happen to have any suggestion for me?

Thank you Marco

Answer 1

Try this:

select t.date,
  count(r.date)
from records r
right join
  ( select date from records where date > '2017-01-01' group by date
  ) t
on r.date      = t.date
and r.typology = 999
group by t.date
order by t.date asc;