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.