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