MySQL select by month returns NULL for all months

Question

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 1

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