include “0” count result in clickhouse

I have one t_table(some_date, id), and i want get count of ids for each day between two input dates.

I try this query:

SELECT dateDiff('day', toDateTime('2020-07-01 07:34:22'), some_date) as day,
       count(distinct id) as ids,
from t_table
where (some_date between '2020-07-01 00:09:25' and '2020-07-15 21:09:25') group by day order by day;

This query returns day only if id has (some_date) for this day. I want get column of all days between input dates, and if no id in some day in ids column put 0 to that cell.

I think that needed to use some join, but i don’t know what join with.

Answer

order by … with fill

SELECT dateDiff('day', toDateTime('2020-07-01 07:34:22'), some_date) as day,
       count() as ids
from (select toDateTime(arrayJoin(['2020-07-02 00:09:25','2020-07-02 00:10:25','2020-07-15 00:00:25'])) some_date)
where (some_date between '2020-07-01 00:09:25' and '2020-07-15 21:09:25') group by day order by day
┌─day─┬─ids─┐
│   1 │   2 │
│  14 │   1 │
└─────┴─────┘


SELECT dateDiff('day', toDateTime('2020-07-01 07:34:22'), some_date) as day,
       count() as ids
from (select toDateTime(arrayJoin(['2020-07-02 00:09:25','2020-07-02 00:10:25','2020-07-15 00:00:25'])) some_date)
where (some_date between '2020-07-01 00:09:25' and '2020-07-15 21:09:25') group by day order by day
with fill;
┌─day─┬─ids─┐
│   1 │   2 │
│   2 │   0 │
│   3 │   0 │
│   4 │   0 │
│   5 │   0 │
│   6 │   0 │
│   7 │   0 │
│   8 │   0 │
│   9 │   0 │
│  10 │   0 │
│  11 │   0 │
│  12 │   0 │
│  13 │   0 │
│  14 │   1 │
└─────┴─────┘