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 ?

  • Please provide your table structures as DDL and your table data as DML, or better yet, use a fiddle. There are a few articles on how to ask questions here on my profile - you might want to take a look? p.s. welcome to the forum! :-) – Vérace Jan 12 at 7:30
0

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
| improve this answer | |
  • Caution: recursion depth will limit the number of days you can build. – Rick James Jan 14 at 4:05
  • You can probably get rid of the outermost SELECT if you change the UNION ALL to RIGHT JOIN ... ON ... – Rick James Jan 14 at 4:09
  • @RickJames: no, right join will not work here... – Luuk Jan 14 at 13:31
  • @RickJames: true, but max recursion depth can be changed, see: stackoverflow.com/questions/32083925/… and/or MAXRECURSION or this – Luuk Jan 14 at 13:33
  • Those 3 links apply only to SQL Server; this Question is tagged "mysql". – Rick James Jan 14 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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