Abrar Jahin Abrar Jahin - 4 months ago 13
SQL Question

MySQL - Get Count value to 0 for Group By Date Value

I have a table like this-

CREATE TABLE sent_bulletin_list
(
`id` int,
`date` found_time,
`value` int,
);


And I like to have a result of finding last 7 day total no of entries day by day.

What I have done is -

SELECT
DATE(found_time) as date,
count( * ) as total
FROM
sent_bulletin_list
WHERE
found_time > ( UNIX_TIMESTAMP() - ( 7 * 24 * 60 * 60 ) )
GROUP BY
DATE(found_time);


And finding something like this-

+------------+-------+
| date | total |
+------------+-------+
| 2016-07-01 | 8 |
+------------+-------+


But I like to have something like this-

+------------+-------+
| date | total |
+------------+-------+
| 2016-06-25 | 0 |
| 2016-06-26 | 0 |
| 2016-06-27 | 0 |
| 2016-06-28 | 0 |
| 2016-06-29 | 0 |
| 2016-06-30 | 0 |
| 2016-07-01 | 8 |
+------------+-------+


Update-



I tried it with
case
like this-

count( CASE found_time IS NOT NULL
THEN 1
ELSE 0
END AS date )


But it is also not working.

Can anyone please help?

Thanks in advance for helping.

Answer

You need a table where all the dates specified in the date range in your query resides.

You may give it a try:

SELECT 
dateTable.day,
COALESCE(your_query.total,0) AS total
FROM 
(
    SELECT DATE(ADDDATE(FROM_UNIXTIME(UNIX_TIMESTAMP() - ( 7 * 24 * 60 * 60 )), INTERVAL @i:=@i+1 DAY)) AS DAY
    FROM (
    SELECT a.a
    FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    JOIN (SELECT @i := -1) r1
    WHERE 
    @i < DATEDIFF(FROM_UNIXTIME(UNIX_TIMESTAMP()), FROM_UNIXTIME(UNIX_TIMESTAMP() - ( 7 * 24 * 60 * 60 )))
) dateTable

LEFT JOIN 
(
    SELECT
    DATE(found_time) as date,
    count( * ) as total
    FROM
    sent_bulletin_list
    WHERE
    found_time > ( UNIX_TIMESTAMP() - ( 7 * 24 * 60 * 60 ) )
    GROUP BY    DATE(found_time)
) your_query
ON dateTable.day = your_query.date
ORDER BY dateTable.day

WORKING DEMO

Test:

Suppose you have the following data in your table:

DROP TABLE IF EXISTS `sent_bulletin_list`;
CREATE TABLE `sent_bulletin_list` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `found_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);
INSERT INTO `sent_bulletin_list` VALUES ('1', '2016-07-17 00:00:00');
INSERT INTO `sent_bulletin_list` VALUES ('2', '2016-07-17 00:00:00');
INSERT INTO `sent_bulletin_list` VALUES ('3', '2016-07-17 00:00:00');
INSERT INTO `sent_bulletin_list` VALUES ('4', '2016-07-17 00:00:00');
INSERT INTO `sent_bulletin_list` VALUES ('5', '2016-07-17 00:00:00');

Then running the above query will give you the following output:

Output:

day         total
2016-07-14  0
2016-07-15  0
2016-07-16  0
2016-07-17  5
2016-07-18  0
2016-07-19  0
2016-07-20  0
2016-07-21  0

Note:

You need to put your end and start date range here:

@i < DATEDIFF(FROM_UNIXTIME(UNIX_TIMESTAMP()), FROM_UNIXTIME(UNIX_TIMESTAMP() - ( 7 * 24 * 60 * 60 )))

And you need to put your start date here:

SELECT DATE(ADDDATE(FROM_UNIXTIME(UNIX_TIMESTAMP() - ( 7 * 24 * 60 * 60 )), INTERVAL @i:=@i+1 DAY)) AS DAY