Is it possible to make count null when using GROUP BY?

Question

MySQL

Greetings!

Let's say I make the following request:

SELECT
    DATE(date_field),
    count(*),
    person
FROM
    some_table
GROUP BY
    date_field, person

If there are no records with some person for one of the dates, then, accordingly, there are no rows with the ratio date_field - person - count either. Is it possible somehow to make this line appear with count = 0?

Thank you!

Answer 1

1. you need a table - a calendar, or a generated set of dates
2. either you need to make a left join of your table to the calendar, or you need to make a right join of the calendar to your table.
SQL join in examples with a description
will result in:

select cal.cal_date, count(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

MariaDB has a Sequence Storage Engine , but prior to MariaDB 10.0, it was shipped as a dynamic plugin, since MariaDB 10.1, the Sequence engine is installed by default. Using it, you can generate a list of dates (calendar) like this:

SELECT '2021-11-01' + INTERVAL seq DAY FROM seq_0_to_29;

and the request will be accordingly:

select cal.cal_date, count(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
;

see an example of work on dbfiddle.uk
and if person is added to the grouping - an example