Counts from case staments

1

Hello,

I run this code and it does almost what I want but it returns the same count for every category even though that is wrong.

What am I doing wrong here?

select

Community
,count(Estate) EstateCount
,count(Garage) GrageCount
,count(Laned) LanedCount
,count(Duplex) DuplexCount
,count(Towns) TownsCount

from (

select

C.community
,(case when LotTypeDesc9 = 'Estate' then 1
else 0 end) as Estate
,(case when LotTypeDesc9 = 'Garage' then 2
else 0 end) as Garage
,(case when LotTypeDesc9 = 'Laned' then 3
else 0 end) as Laned
,(case when LotTypeDesc9 = 'Duplex' then 4
else 0 end) as Duplex
,(case when LotTypeDesc9 = 'Townhomes' then 5
else 0 end) as Towns

from tblcustomers as c
inner join tblLotinventory l
on c.job_no = l.job_no

WHERE
home_selection = 'PreSale'
and presale_selection <> 'Lot Only'
and cancelled = 0
and c.job_no <>''

and approved = 1
and inactive = 0
and sold = 0
and purchased = 1
and divisionid in ('1','3')

) as prequery

group by cOmmunity

2

"COUNT()" just checks to see if a value exists. You need to check whether it's 0 or not, so do something like this:

select
Community
,case when Estate = 0 then 0 else 1 end EstateCount
,case when Garage = 0 then 0 else 1 end GarageCount
,...
from ...