Query won't return results when 0 is returned

Question

I need this query to return results when the count is 0 instead of just producing an empty set. How can I adjust my query so that it produces the following table with a '0' for the count and the appropriate date?

mysql> select count(test.id), date(insert_datetime) date 
from db.table test where date(insert_datetime)='2015-08-17' 
group by date(insert_datetime);
    +--------------+------------+
    | count(test.id) | date     |
    +--------------+------------+
    |           42 | 2015-08-17 |
    +--------------+------------+
    1 row in set (0.14 sec)


mysql> select count(test.id), date(insert_datetime) date 
from db.table test where date(insert_datetime)='2015-08-16' 
group by date(insert_datetime);

Empty set (0.00 sec)

If you have to do `date(insert_datetime)`, what is the datatype of that field? — Uueerdo, Sep 15 '15 at 00:21
You're only ever returning a max of 1 row, correct? Try removing the group by clause. — ESG, Sep 15 '15 at 00:21
Ok, just making sure it is not string... It won't change the results but doing `WHERE insert_datetime BETWEEN 20150817000000 AND 20150817235959` could be significantly faster. — Uueerdo, Sep 15 '15 at 00:23
It is not the case that "0 is returned". Rather the *missing groups are not present at all*. See http://stackoverflow.com/questions/75752/what-is-the-most-straightforward-way-to-pad-empty-dates-in-sql-results-on-eithe?lq=1 , http://stackoverflow.com/questions/1046865/mysql-select-all-dates-in-a-range-even-if-no-records-present?lq=1 , http://stackoverflow.com/questions/2978129/retrieve-missing-dates-from-database-via-mysql?lq=1 — user2864740, Sep 15 '15 at 00:24

Uueerdo · Answer 1 · 2015-09-15T00:41:24.473

This should do it:

SELECT theDate AS `date`, IFNULL(subC.theCount, 0) AS `theCount`
FROM (SELECT DATE(20150817) AS `theDate`) AS subD
LEFT JOIN (
   SELECT COUNT(test.id) AS theCount, DATE(insert_datetime) AS `theDate` 
   FROM db.table AS test 
   WHERE insert_datetime BETWEEN 20150817000000 AND 20150817235959 
   GROUP BY theDate
) AS subC
USING (theDate)
;

As another user hinted in a now deleted comment: if you are going to need this for a date range, an "all dates" table may come in more handy than the subD subquery; making a SELECT DATE(X) UNION SELECT DATE(Y) UNION SELECT DATE(Z) UNION SELECT DATE(etc...) subquery gets ridiculous fairly quickly.

It will return a row with a date field = 20150817 and a "count" field = to the number of records found, or 0 if none were. Exactly what the question wanted. — Uueerdo, Sep 15 '15 at 00:36

Query won't return results when 0 is returned

1 Answers1