CODEWITHSUNDEEP
sqlsql-server
ajburnett344
ajburnett344

Reputation: 79

Create Multiple Count Columns using Case When

I have to Case statements that count the same column just with a differnt criteria. The problem i am having is that the first case creates a null entry in the Test2 column but then counts it on my second case statement and leaves the Test 1 column null. I would like to have both counts side by side instead of created a duplicate row.

select m.no,  
Case when itemtype = 'S' THEN count(ITEMKEY) end as Test1,
case when ItemType='C' THEN count(ITEMKEY) END as Test2
from test m

Upvotes: 0

Views: 1247

Answers (2)

PNJ
PNJ

Reputation: 1

Following query can display records which having Itemtype 'S' or 'C' with count of itemkey. if itemkey is null it will display 0 else count of item key 

select m.no,  
Case when isnull( m.itemtype = 'S',0) THEN (select count(a.ITEMKEY) from test a where  a.itemtype = 'S' ) else 0 end as Test1,
case when isnull( m.itemtype='C',0) THEN (select count(b.ITEMKEY) from test b where  b.itemtype = 'C') else 0 END as Test2
from test m

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270091

I'm pretty sure you want conditional aggregation. The case expression is an argument to the aggregation function:

select m.no, 
       sum(case when itemtype = 'S' then 1 else 0 end) as test1,
       sum(case when itemtype = 'C' then 1 else 0 end) as test2
from test m
group by m.no;

This assumes that itemKey is never null, so the count() is just counting rows.

Upvotes: 2

Related Questions