Tuesday, 28 August 2018

LEFT OUTER JOIN does not return null values

I have a pretty simple sample query that I am trying to complete.

SELECT
    month(s.report_date),
    COALESCE(COUNT(*),0)
FROM
    stat_summary s LEFT OUTER JOIN ref_months m on MONTH(s.report_date) = m.month_id
GROUP BY month(s.report_date)

My results look like:
My desired results would be something like:
month  | count
----------------
    1  | 0
    2  | 0
    3  | 0
    4  | 0
    5  | 0
    6  | 0
    7  | 0
    8  | 0
    9  | 4
    10 | 9
    11 | 0
    12 | 0


You need to use the months table as the primary one:
SELECT
    m.month_id,
    COALESCE(COUNT(s.report_date),0)
FROM ref_months m
LEFT JOIN stat_summary s
    ON MONTH(s.report_date) = m.month_id
GROUP BY m.month_id;

0 comments:

Post a Comment