By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table some_table(
date_field date,
person char(10)
)
;
insert into some_table values ('2021-11-01','Ivanov');
insert into some_table values ('2021-11-01','Ivanov');
insert into some_table values ('2021-11-11','Ivanov');
insert into some_table values ('2021-11-01','Petrov');
insert into some_table values ('2021-11-01','Petrov');
insert into some_table values ('2021-11-11','Sidorov');
insert into some_table values ('2021-11-11','Sidorov');
insert into some_table values ('2021-11-15','Ivanov');
insert into some_table values ('2021-11-05','Sidorov');
select * from some_table;
| date_field | person |
|---|---|
| 2021-11-01 | Ivanov |
| 2021-11-01 | Ivanov |
| 2021-11-11 | Ivanov |
| 2021-11-01 | Petrov |
| 2021-11-01 | Petrov |
| 2021-11-11 | Sidorov |
| 2021-11-11 | Sidorov |
| 2021-11-15 | Ivanov |
| 2021-11-05 | Sidorov |
select cal.cal_date, count(person), person
from some_table st
right join (
-- генерируем календарь - набор дат
select *
from (
select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) cal_date
from
(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
) v
where cal_date between '2021-11-01' and '2021-11-30'
) cal on cal.cal_date = st.date_field
group by cal.cal_date, person
;
| cal_date | count(person) | person |
|---|---|---|
| 2021-11-01 | 2 | Ivanov |
| 2021-11-01 | 2 | Petrov |
| 2021-11-02 | 0 | null |
| 2021-11-03 | 0 | null |
| 2021-11-04 | 0 | null |
| 2021-11-05 | 1 | Sidorov |
| 2021-11-06 | 0 | null |
| 2021-11-07 | 0 | null |
| 2021-11-08 | 0 | null |
| 2021-11-09 | 0 | null |
| 2021-11-10 | 0 | null |
| 2021-11-11 | 1 | Ivanov |
| 2021-11-11 | 2 | Sidorov |
| 2021-11-12 | 0 | null |
| 2021-11-13 | 0 | null |
| 2021-11-14 | 0 | null |
| 2021-11-15 | 1 | Ivanov |
| 2021-11-16 | 0 | null |
| 2021-11-17 | 0 | null |
| 2021-11-18 | 0 | null |
| 2021-11-19 | 0 | null |
| 2021-11-20 | 0 | null |
| 2021-11-21 | 0 | null |
| 2021-11-22 | 0 | null |
| 2021-11-23 | 0 | null |
| 2021-11-24 | 0 | null |
| 2021-11-25 | 0 | null |
| 2021-11-26 | 0 | null |
| 2021-11-27 | 0 | null |
| 2021-11-28 | 0 | null |
| 2021-11-29 | 0 | null |
| 2021-11-30 | 0 | null |
SELECT '2021-11-01' + INTERVAL seq DAY FROM seq_0_to_29;
| '2021-11-01' + INTERVAL seq DAY |
|---|
| 2021-11-01 |
| 2021-11-02 |
| 2021-11-03 |
| 2021-11-04 |
| 2021-11-05 |
| 2021-11-06 |
| 2021-11-07 |
| 2021-11-08 |
| 2021-11-09 |
| 2021-11-10 |
| 2021-11-11 |
| 2021-11-12 |
| 2021-11-13 |
| 2021-11-14 |
| 2021-11-15 |
| 2021-11-16 |
| 2021-11-17 |
| 2021-11-18 |
| 2021-11-19 |
| 2021-11-20 |
| 2021-11-21 |
| 2021-11-22 |
| 2021-11-23 |
| 2021-11-24 |
| 2021-11-25 |
| 2021-11-26 |
| 2021-11-27 |
| 2021-11-28 |
| 2021-11-29 |
| 2021-11-30 |
select cal.cal_date, count(person), person
from some_table st
right join (
-- генерируем календарь - набор дат
SELECT '2021-11-01' + INTERVAL seq DAY as cal_date FROM seq_0_to_29
) cal on cal.cal_date = st.date_field
group by cal.cal_date, person
;
| cal_date | count(person) | person |
|---|---|---|
| 2021-11-01 | 2 | Ivanov |
| 2021-11-01 | 2 | Petrov |
| 2021-11-02 | 0 | null |
| 2021-11-03 | 0 | null |
| 2021-11-04 | 0 | null |
| 2021-11-05 | 1 | Sidorov |
| 2021-11-06 | 0 | null |
| 2021-11-07 | 0 | null |
| 2021-11-08 | 0 | null |
| 2021-11-09 | 0 | null |
| 2021-11-10 | 0 | null |
| 2021-11-11 | 1 | Ivanov |
| 2021-11-11 | 2 | Sidorov |
| 2021-11-12 | 0 | null |
| 2021-11-13 | 0 | null |
| 2021-11-14 | 0 | null |
| 2021-11-15 | 1 | Ivanov |
| 2021-11-16 | 0 | null |
| 2021-11-17 | 0 | null |
| 2021-11-18 | 0 | null |
| 2021-11-19 | 0 | null |
| 2021-11-20 | 0 | null |
| 2021-11-21 | 0 | null |
| 2021-11-22 | 0 | null |
| 2021-11-23 | 0 | null |
| 2021-11-24 | 0 | null |
| 2021-11-25 | 0 | null |
| 2021-11-26 | 0 | null |
| 2021-11-27 | 0 | null |
| 2021-11-28 | 0 | null |
| 2021-11-29 | 0 | null |
| 2021-11-30 | 0 | null |