CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ebjdnh
ebjdnh

Reputation: 11

Count to show 0 when no results

I have the following query 

select dc.dateasdate as 'Date', count(Practice) as 'Total Enquiries', dp.practice, de.Origin
from dim.Practice dp
    left join fact.enquiry fe on fe.PracticeSKey = dp.PracticeSKey
        left join dim.EnquiryOrigin de on de.EnquiryOriginSKey = fe.EnquiryOriginSKey and de.Origin = 'Web Enquiry'
            left join dim.Calendar dc on dc.CalendarSKey = fe.EnquiryCreatedSKey
                where dc.dateasdate = '2016-04-03' 
group by dc.dateasdate , de.Origin,practice
order by dc.dateasdate ,Practice

It won't show when there's a 0 Practice.

Can anyone help me? I've been stuck on this for a few days.

Upvotes: 0

Views: 61

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

The WHERE condition effectively turns the LEFT JOIN into an INNER JOIN. You should put the condition in the last LEFT JOIN:

SELECT
    dc.dateasdate AS 'Date',
    COUNT(Practice) AS 'Total Enquiries',
    dp.practice,
    de.Origin
FROM dim.Practice dp
LEFT JOIN fact.enquiry fe
    ON fe.PracticeSKey = dp.PracticeSKey
LEFT JOIN dim.EnquiryOrigin de
    ON de.EnquiryOriginSKey = fe.EnquiryOriginSKey
    AND de.Origin = 'Web Enquiry'
LEFT JOIN dim.Calendar dc
    ON dc.CalendarSKey = fe.EnquiryCreatedSKey
    AND dc.dateasdate = '2016-04-03'
GROUP BY
    dc.dateasdate,
    de.Origin,
    practice
ORDER BY dc.dateasdate, Practice

Upvotes: 5

Related Questions