Frederick Behrends Frederick Behrends - 1 month ago 6
MySQL Question

Mysql group by weekday, fill in missing weekdays

I have a problem with a MySQL query, that groups data in my table by weekday.

I need it to fill in weekdays that a missing in the data, for example sunday (weekday 7) in the SQL-example below.

SQL Fiddle

MySQL 5.6 Schema Setup:

create table test (
`id` INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`date` DATETIME
);

INSERT INTO test (`date`) VALUES
('2016-05-16 00:00:00'),
('2016-05-17 00:00:00'),
('2016-05-18 00:00:00'),
('2016-05-20 00:00:00'),
('2016-05-21 00:00:00'),
('2016-05-22 00:00:00'),
('2016-05-16 00:00:00'),
('2016-05-17 00:00:00'),
('2016-05-18 00:00:00'),
('2016-05-20 00:00:00');


Query 1:

SELECT WEEKDAY(date) AS weekday,
COUNT(id) AS posts
FROM test
GROUP BY WEEKDAY(date)


Results:

| weekday | posts |
|---------|-------|
| 0 | 2 |
| 1 | 2 |
| 2 | 2 |
| 4 | 2 |
| 5 | 1 |
| 6 | 1 |


I expect it return this line too.

| 3 | 0 |


My complete query i quite complex so i hope you find a quick solution for this.

Answer

The normal approach is a left join:

select wd.wd, count(t.id)
from (select 1 as wd union all select 2 union all select 3 union all select 4 uion all
      select 5 union all select 6 union all select 7
     ) wd left join
     test t
     on wd.wd = weekday(t.date)
group by wd.wd
order by wd.wd;
Comments