CODEWITHSUNDEEP
sqlcountaggregate
TheProgrammer
TheProgrammer

Reputation: 1344

Display rows that have a zero count

I am trying to display rows even if they return a count of zero. However no luck. I tried using left join.

select
    a.Month,
    count(b.InsuranceFromJob) [Number of Participants without Insurance]
from
    hsAdmin.ReportPeriodLkup a
left join hsAdmin.ClientReport b on
    b.ReportPeriod = a.ReportPeriodId
where
    b.insurancefromjob = 2 and
    a.reportperiodid between (@lastReportId - 11) and @lastReportId
group by
    a.Month

Upvotes: 0

Views: 50

Answers (1)

Theo Verweij
Theo Verweij

Reputation: 63

Because clientreport is in the where, only rows that exists in clientreport will be in the resultset.

Move the check to the join and you will get the desired result:

select
    a.Month,
    count(b.InsuranceFromJob) [Number of Participants without Insurance]
from
    hsAdmin.ReportPeriodLkup a
left join hsAdmin.ClientReport b on
    b.ReportPeriod = a.ReportPeriodId
and b.insurancefromjob = 2
where
    a.reportperiodid between (@lastReportId - 11) and @lastReportId
group by
    a.Month

Upvotes: 1

Related Questions