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.
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 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();