Thursday, October 6, 2011

Condition Based Count in Sqlserver- CountIf()

I have been wondering if there is a way to count records based on conditions ,

something like countif() we can use


EnumId EnumTypeCode

1 code1

2 code2

3 code3


TransactionId TransactionTypeId EnumId AmountPaid

1 1 1 200

2 1 2 400

3 1 1 300

if in output we want something like this get the count of code1 , code2 and code3 per transactionTypeId .

TransactionTypeId Code1 Code2 Code3

1 2 1 0

if we need the output mentioned above we must need a condition based Count so for this purpose one of the way i got on net is below:

Declare @code1 int

Declare @code2 int

Declare @code3 int

set @code1 =1

set @code2 =2

set @code3=3

select TransactionId, sum ( case when isnull (Enumid,0)=@code1 then 1 else 0 End ) as Code1,

sum(case when isnull(Enumid,0)=@code2 then 1 else 0 End) as Code2

, sum(case when isnull(Enumid,0)=@code3 then 1 else 0 End) as Code3

From Transactions

Group by TransactionId


Yashpal Sharma

Wants to be happy, Dont expect anything to anyone.

No comments:

Post a Comment