Query problem

I having trouble with the query below. I'm trying to
return the number of calls base on severity. The
#TempTable has 3 severities listed (1,2,&3). There are
no calls for severity 1, but i still want it to return a
record with a count of 0. A sample of what the query is
returning is at the bottom of this message. Please help.
select
Call.severity,
count(call.callno) as callcount
from
Call
left join Severity
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Call.severity
severity callcount
-- --
2 4
3 25
Since you've posted no DDL or sample data I can't say for sure, but you can
try:
select
Severity.severityID,
SUM(CASE WHEN Call.Severity = Severity.severityID THEN 1 ELSE 0 END) as
callcount
from
Severity
left join Call
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Severity.severityID
"Vic" <vduran@.specpro-inc.com> wrote in message
news:179e501c421a7$41aa2060$a001280a@.phx.gbl...
> I having trouble with the query below. I'm trying to
> return the number of calls base on severity. The
> #TempTable has 3 severities listed (1,2,&3). There are
> no calls for severity 1, but i still want it to return a
> record with a count of 0. A sample of what the query is
> returning is at the bottom of this message. Please help.
>
> select
> Call.severity,
> count(call.callno) as callcount
> from
> Call
> left join Severity
> On Call.Severity = Severity.severityID
> where
> datetimesubmitted between '01/01/2004' and '01/31/2004'
> and
> status <> 'CANCELLED'
> group by Call.severity
>
> severity callcount
> -- --
> 2 4
> 3 25
sql