CODEWITHSUNDEEP
sqlsql-serversql-server-2008
etm124
etm124

Reputation: 2140

LEFT OUTER JOIN not returning NULL values

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

SQL Fiddle

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:

enter image description here

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

Upvotes: 0

Views: 1835

Answers (4)

Lamak
Lamak

Reputation: 70648

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;

Upvotes: 3

pancho018
pancho018

Reputation: 587

if the months dont always exist in your ref_months table you can create using the Master db.

Select m.Number as m_MonthID
COALESCE(COUNT(*),0) as count
from FROM Master.dbo.spt_Values m
left join stat_summary s 
ON MONTH(s.report_date) = m.Number
where m.name is null
and m.number between 1 and 12
GROUP BY m.Number

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can try this. As already answered, months should be the table to be left joined upon. Also, the count should be on adp column or the month(s.report_date) column in stat_summary table.

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

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

You can change LEFT JOIN table order, and there is no need for COALESCE:

SqlFiddleDemo

SELECT
    m.month_id,
    [count] = COUNT(s.report_date)
FROM ref_months m 
LEFT JOIN stat_summary s 
    ON MONTH(s.report_date) = m.month_id
GROUP BY m.month_id;

Upvotes: 0

Related Questions