CODEWITHSUNDEEP
sqlsumcase
Sheri G
Sheri G

Reputation: 47

Sum (or count) multiple case statement

I have the following table:

Month | Item | Events | Party | Spirit | Faith |
May | 123 | 1 | 1 | 0 | 0 |
June |123 | 1 | 0 | 1 | 1 |

it is basically 1 for yes 0 for no. I need to know how many different categories each item is in each month

I need the following results:

Month | Item | Counts |
May | 123 | 2 |
June| 123 | 3 |

This is NOT working:

select Month, Item,
       sum(case when EVENTS = 1 then 1 when PARTY = 1 then 1 when SPIRIT = 1 then 1 when FAITH = 1 then 1 else 0 end) as Counts
from TABLE
group by 1,2

Please help, thanks!

Upvotes: 0

Views: 545

Answers (2)

AlexanderBebe
AlexanderBebe

Reputation: 1

CREATE TABLE #T
(
Month varchar(10), Item int, Events bit, Party bit, Spirit bit , Faith bit
)
insert into #T
SELECT 'May' , 123 , 1 , 1 , 0 , 0  union
SELECT 'June' ,123 , 1 , 0 , 1 , 1 

select Month, Item, CAST(Events AS INT) + CAST(Party AS INT)+ CAST(Spirit AS 
INT) +CAST(Faith AS INT)  from #T

Aggregation is not needed. Since the events, party, spirit and faith are bit columns, we need to cast it to int and then add it.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You don't need aggregation:

select Month, Item,
       (events + party + spirit + faith) as counts
from t;

Upvotes: 2

Related Questions