Include NULL Results as 0 in MySQL

Question

My original query is as follows:

SELECT COUNT(*) AS count, YEAR(created_at) AS "year", MONTH(created_at) AS "month"
FROM quotes WHERE
(YEAR(created_at) = YEAR(CURDATE()) AND MONTH(created_at) = MONTH(CURDATE())
OR YEAR(created_at) = YEAR(CURDATE()) AND MONTH(created_at) = MONTH(CURDATE()) -1)
AND status_id = 1
GROUP BY YEAR(created_at), MONTH(created_at) DESC

This query basically retrieves the COUNT for this month and the previous month and works fine except when there are no results for either month.

I have two similar queries that do the same except for weeks and years.

I've tried to use COALESCE and IFNULL but it doesn't seem to include NULL results.

SELECT IFNULL(COUNT(*), 0) AS count, YEAR(created_at) AS "year", MONTH(created_at) AS "month"
FROM quotes
WHERE
    (YEAR(created_at) = YEAR(CURDATE()) AND MONTH(created_at) = MONTH(CURDATE()) OR YEAR(created_at) = YEAR(CURDATE()) AND MONTH(created_at) = MONTH(CURDATE()) -1)
    AND status_id = 1
GROUP BY YEAR(created_at), MONTH(created_at) DESC

Actual Result

count  |  year  | month
-----------------------
    1  |  2014  |  11

Expected Result

count  |  year  | month
-----------------------
    1  |  2014  |  11
    0  |  2014  |  10

SQL Fiddle

@jbarker2160 nope, not 100% sure what you're suggesting but I have an idea and will give it a try — martincarlin87, Nov 25 '14 at 13:40
You cannot select what is not there. You have to create or simulate missing data in some way. — Strawberry, Nov 25 '14 at 13:46
Something like this,just fill that table with all 12 months http://sqlfiddle.com/#!2/beac3f/14 — Mihai, Nov 25 '14 at 13:50

score 0 · Answer 1 · edited May 23 '17 at 12:12

0

You need to use a seperate data-set for the months you want to see. Your SQLfiddle simply is grouping the rows on Quotes, and as such shows no aggregate rows for months that don't show up in the result set at all.

Try Creating a temporary table with the months you want, and then do a simple OUTER JOIN to select any quotes that appear.

edited May 23 '17 at 12:12

Community

1
1

answered Nov 25 '14 at 13:40

DougM

2,808
17
14

score 0 · Answer 2 · answered Nov 25 '14 at 13:49

Just moving the IFNULL inside the SUM function, like a comment suggested, won't be enough. You can only group by values that are available. If there is no entry for 2014-10 you won't get anything printed. Otherwise you would see every possible combination.

Note: The where clause only limits the results, it has no effect on what to show. The Select doens't "know" that it has to show the month 10. becuas ethere is no value there.

I don't know what do you want to achieve, but personally in such a case I would just print 0 in the program when there is no matching result at all.

score 0 · Answer 3 · answered Nov 25 '14 at 13:54

0

At its simplest...

SELECT x.month
     , COUNT(q.id) total
  FROM (SELECT 10 month UNION SELECT 11) x 
  LEFT
  JOIN quotes q 
    ON MONTH(created_at) = month
 GROUP 
    BY month;

answered Nov 25 '14 at 13:54

Strawberry

33,750
13
40
57

Include NULL Results as 0 in MySQL

3 Answers3