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




EnumTransactionType


EnumId EnumTypeCode


1 code1


2 code2


3 code3




Transactions


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




Thanks


Yashpal Sharma


Wants to be happy, Dont expect anything to anyone.

No comments:

Post a Comment