0

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 ?

0

1 Answer 1

2

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
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.