How to group by date (SELECT if null then 0) MYSQL

Question

How do I get counts data grouped by every day in range date even if data is not present i.e. IF 0 will select 0

This my sample data and query MYSQL FIDDLE

Input

create table t_order(
  order_id int
  ,qty int
  ,time datetime 
);

insert into t_order values (1,2,'2020-02-17 08:00:00.000');
insert into t_order values (2,1,'2020-02-18 10:00:00.000');
insert into t_order values (3,3,'2020-02-20 08:00:00.000');
insert into t_order values (4,1,'2020-02-20 10:00:00.000');
insert into t_order values (5,7,'2020-02-21 08:00:00.000');
insert into t_order values (6,1,'2020-02-23 10:00:00.000');
insert into t_order values (7,1,'2020-02-24 08:00:00.000');
insert into t_order values (8,2,'2020-02-24 10:00:00.000');
insert into t_order values (9,1,'2020-02-25 08:00:00.000');
insert into t_order values (10,1,'2020-02-26 10:00:00.000');

Implementation

SELECT
  SUM(qty) as total,
  DATE(time) as date
FROM t_order
where 
  DATE(TIME) between date('2020-02-15') and date('2020-02-26')
GROUP BY CAST(time AS date)

Actual Output:

total   date
2   2020-02-17
1   2020-02-18
4   2020-02-20
7   2020-02-21
1   2020-02-23
3   2020-02-24
1   2020-02-25
1   2020-02-26

My expectation code show every date by range date parameter event data is 0 or null:

Expected Output

  total date
0   2020-02-15
0   2020-02-16
2   2020-02-17
1   2020-02-18
0   2020-02-19
4   2020-02-20
7   2020-02-21
0   2020-02-22
1   2020-02-23
3   2020-02-24
1   2020-02-25
1   2020-02-26

score 0 · Answer 1 · answered Mar 02 '20 at 10:24

0

You can only select data that exists in the database.

To achieve what you want, create a separate table (a temporary one or permanent calendar table) that contains all the dates in your desired date range and include that (join) with your query.

answered Mar 02 '20 at 10:24

slaakso

8,331
2
16
27

score 0 · Answer 2 · answered Mar 03 '20 at 01:49

You should create a calendar table. If you're using MariaDB (v10.1 above), there's an easy way to generate a temporary calendar table. With MySQL however, you'll going to use longer query (not sure in latest MySQL version though). Refer following example:

SELECT ADDDATE('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) as dates FROM
(SELECT 0 i 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 i 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 i 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 i 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 i 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;

This will generate a date range from '1970-01-01' to '2243-10-16' in MySQL but of course you can change it accordingly. To create a calendar table, I've prepared a fiddle here : https://www.db-fiddle.com/f/qVgmfDaVUszABAyFzH7iUS/3

I have also previously provide a suggestion for shorter date range here : https://stackoverflow.com/a/58443510/10910692 inclusive of MariaDB suggestion; which is much shorter query to generate dates/calendar.

Now if you don't create a calendar table, using this method your query will look something like this:

SELECT IFNULL(B.Total,0) AS Total, A.dates AS Date FROM
(SELECT ADDDATE('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) as dates FROM
(SELECT 0 i 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 i 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 i 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 i 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 i 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) A 
LEFT JOIN
(SELECT
  SUM(qty) as total,
  DATE(time) as date
FROM t_order
where 
  DATE(TIME) between date('2020-02-15') and date('2020-02-26')
GROUP BY CAST(time AS date)) B 
ON A.dates=B.date
WHERE A.dates BETWEEN '2020-02-15 and '2020-02-26';

It looks ridiculously long and unnecessary. But with a calendar table, you just need to do:

SELECT IFNULL(B.Total,0) AS Total, A.dates AS Date 
FROM calendar A
LEFT JOIN
(SELECT
  SUM(qty) as total,
  DATE(time) as date
FROM t_order
where 
  DATE(TIME) between date('2020-02-15') and date('2020-02-26')
GROUP BY CAST(time AS date)) B 
ON A.dates=B.date
WHERE A.dates BETWEEN '2020-02-15 and '2020-02-26';

How to group by date (SELECT if null then 0) MYSQL

2 Answers2