quote:

---

Originally posted by Karen426

I need to count the number of rings in a region.
My query does that but this is for an SSRS report so when I run the report I want it to display a 0 where there is no rings in that region
but currently I am only getting regions where the count is greater or equal to 1

select
plantype
, planyear
, region
, Case
When count(ring) < 1 then 0
else count(ring)
End as ringcnt

From A
where planyear > 2011
group by
plantype
, planyear
, region

Currently my data displays as
A 2012 Central 12
Northeast 4

I want it to display as
A 2012 Central 12
South 0
Northeast 4
West 0

Thanks ahead for helping me

---

select t.plantype,t.planyear,t.region,
 count(ring) as ringcnt
from
(
select plantype,planyear,region
from (select distinct plantype,planyear from A)m
cross join (select distinct region from A)n
)t
left join  A a
on a.plantype = t.plantype
and a.planyear = t.planyear
and a.region = t.region
group by t.plantype,t.planyear,t.region