G_V G_V - 4 months ago 15
SQL Question

MySQL select by month returns NULL for all months

Using MySQL 5.6 I am trying to select the total

inspections
for each
month
in the past 12 months with an output of 0 if there are none. I appear to be missing something here as the output is all
NULL
. The
date_inspected
field is just a regular SQL
date


From what I understood, the structure should be
[conditional statement, output variable, default value]
but even the 0 gets ignored in favor of NULL. I am trying to understand what I am doing wrong here.

Table:

Column Type Null
id int(11) No
inspector_id int(11) Yes
company_id int(11) Yes
date_inspected date No
start_time datetime No
end_time datetime No


Query:

SELECT
SUM(IF(MONTH = 'Jan', total, 0)) AS 'Januari',
SUM(IF(MONTH = 'Feb', total, 0)) AS 'Februari',
SUM(IF(MONTH = 'Mar', total, 0)) AS 'Maart',
SUM(IF(MONTH = 'Apr', total, 0)) AS 'April',
SUM(IF(MONTH = 'May', total, 0)) AS 'Mei',
SUM(IF(MONTH = 'Jun', total, 0)) AS 'Juni',
SUM(IF(MONTH = 'Jul', total, 0)) AS 'Juli',
SUM(IF(MONTH = 'Aug', total, 0)) AS 'Augustus',
SUM(IF(MONTH = 'Sep', total, 0)) AS 'September',
SUM(IF(MONTH = 'Oct', total, 0)) AS 'Oktober',
SUM(IF(MONTH = 'Nov', total, 0)) AS 'November',
SUM(IF(MONTH = 'Dec', total, 0)) AS 'December',
SUM(total) AS all_months
FROM (
SELECT MONTH(date_inspected) AS MONTH, COUNT(*) AS total
FROM inspection
WHERE date_inspected BETWEEN NOW() AND Date_add(NOW(), interval - 12 month)
GROUP BY MONTH
) AS SubTable


Output

{ ["Januari"]=> NULL
["Februari"]=> NULL
["Maart"]=> NULL
["April"]=> NULL
["Mei"]=> NULL
["Juni"]=> NULL
["Juli"]=> NULL
["Augustus"]=> NULL
["September"]=> NULL
["Oktober"]=> NULL
["November"]=> NULL
["December"]=> NULL
["all_months"]=> NULL }


Update:

SOLUTION by Gordon Linoff

SELECT
SUM(CASE WHEN MONTH(date_inspected) = 1 THEN 1 ELSE 0 END) AS 'Januari',
SUM(CASE WHEN MONTH(date_inspected) = 2 THEN 1 ELSE 0 END) AS 'Februari',
SUM(CASE WHEN MONTH(date_inspected) = 3 THEN 1 ELSE 0 END) AS 'Maart',
SUM(CASE WHEN MONTH(date_inspected) = 4 THEN 1 ELSE 0 END) AS 'April',
SUM(CASE WHEN MONTH(date_inspected) = 5 THEN 1 ELSE 0 END) AS 'Mei',
SUM(CASE WHEN MONTH(date_inspected) = 6 THEN 1 ELSE 0 END) AS 'Juni',
SUM(CASE WHEN MONTH(date_inspected) = 7 THEN 1 ELSE 0 END) AS 'Juli',
SUM(CASE WHEN MONTH(date_inspected) = 8 THEN 1 ELSE 0 END) AS 'Augustus',
SUM(CASE WHEN MONTH(date_inspected) = 9 THEN 1 ELSE 0 END) AS 'September',
SUM(CASE WHEN MONTH(date_inspected) = 10 THEN 1 ELSE 0 END) AS 'Oktober',
SUM(CASE WHEN MONTH(date_inspected) = 11 THEN 1 ELSE 0 END) AS 'November',
SUM(CASE WHEN MONTH(date_inspected) = 12 THEN 1 ELSE 0 END) AS 'December'
FROM inspection
WHERE date_inspected BETWEEN Date_add(NOW(), interval - 12 month) AND NOW()


As I understand it, we've given the
SUM()
a conditional CASE statement that if the current record's
date_inspected
's MONTH is equal to the MySQL constant for that value, return true and add it to the total, else do nothing.

More on MySQL CASE

Answer

Juergen is correct on one problem in your query. Another is that MONTH() returns a number, not a string.

And, you can further simplify the query. A subquery is not needed:

    SELECT MONTH(date_inspected) AS MONTH,
           SUM(CASE WHEN MONTH(date_inspected) = 1 THEN 1 ELSE 0 END)) AS 'Januari',
           SUM(CASE WHEN MONTH(date_inspected) = 2 THEN 1 ELSE 0 END)) AS 'Februari',
           . . .
    FROM inspection
    WHERE date_inspected BETWEEN Date_add(NOW(), interval - 12 month) AND NOW()
    GROUP BY MONTH(date_inspected);
Comments