MySQL date range if data not available display 0

dataMySQLquerysubquery

I've a table here here is the structure : id,id_berita,ip_user,tanggal

I try to use this query : 

select count(id) as jumlah,tanggal from tb_trafik_berita WHERE (tanggal BETWEEN '2020-01-02 00:00:00' AND '2020-01-07 23:59:00') group by tanggal

and the output of that query,like this :

tanggal             | jumlah  |
____________________|_________|
2020-01-02 04:13:25 | 1       |
2020-01-02 04:13:39 | 1       |     
2020-01-02 05:02:44 | 1       |
2020-01-03 13:23:22 | 1       |
2020-01-03 13:29:20 | 1       |
2020-01-04 01:36:45 | 1       |
2020-01-04 01:41:30 | 1       |
2020-01-07 14:37:08 | 1       |
2020-01-07 15:05:21 | 1       |

the output only showing data that available on table (only these date : 02,03,04,07)

if I want the output also showing date that not available on table : 02,03,04,05,06,07 , but put value 0,

so the expected output is :

tanggal             | jumlah  |
____________________|_________|
2020-01-02 04:13:25 | 1       |
2020-01-02 04:13:39 | 1       |     
2020-01-02 05:02:44 | 1       |
2020-01-03 13:23:22 | 1       |
2020-01-03 13:29:20 | 1       |
2020-01-04 01:36:45 | 1       |
2020-01-04 01:41:30 | 1       |
2020-01-05 00:00:00 | 0       | <<< set 0 cuz data on table not available
2020-01-06 00:00:00 | 0       | <<< set 0 cuz data on table not available
2020-01-07 14:37:08 | 1       |
2020-01-07 15:05:21 | 1       |

I've try this query : 

SELECT XX.dm AS 'Tanggal',IFNULL(YY.jumlah,0) AS 'Jumlah' FROM
(SELECT dm FROM
(SELECT CONCAT_WS('-',df,CONCAT(b,c)) dm FROM 
(SELECT DATE_FORMAT(CURDATE(), '%Y-%m') df) a, 
(SELECT 0 b UNION SELECT 1 b UNION SELECT 2 UNION SELECT 3) b,
(SELECT 0 c 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) c ) d WHERE DAY(dm) BETWEEN 1 AND DAY(LAST_DAY(dm))) XX
LEFT JOIN
(SELECT COUNT(id) AS jumlah,tanggal 
FROM tb_trafik_berita where id_berita = '15'
GROUP BY tanggal) YY 
ON XX.dm=DATE(YY.tanggal)
#add WHERE clause here
WHERE XX.dm BETWEEN 2020-01-02' AND '2020-01-07' 
ORDER BY XX.dm;

but it's still has bug if i put WHERE (XX.dm BETWEEN '2019-12-13' AND '2020-01-11' ) filter date range in different year it showing like this :

enter image description here

the bug is begin from date 01 at 2020 only…

Anyone can help me to fix the bug ?

Best Answer

You should use a RECURSIVE CTE available in Mysql8.0 and above.

WITH RECURSIVE abc AS (
  select CAST('2020-01-01' as DATE) as a 
  union all 
  select ADDDATE(a,interval 1 day) 
  from abc where a<'2020-01-31')     
SELECT * FROM abc;

Above will output all dates between '2020-01-01' and '2020-01-31'.

If you join this to your query, you should see 0 for dates that have no value in your table.

WITH RECURSIVE abc AS (
  select CAST('2020-01-01' as DATE) as a 
  union all 
  select ADDDATE(a,interval 1 day) 
  from abc where a<'2020-01-31')
select SUM(x.jumlah), x.tanggal  from (
select 
 count(id) as jumlah,
 tanggal 
from tb_trafik_berita 
WHERE (tanggal BETWEEN '2020-01-02 00:00:00' AND '2020-01-07 23:59:00') 
group by tanggal
union all 
select 0,a from abc
) x 
GROUP BY x.tanggal
Related Question