select
[AccountCallId],
sum([SS]) as CountSS,
sum([ST]) as CountST,
sum([SA]) as CountSA,
sum([OC]) as CountOC,
sum([EC]) as CountEC,
sum([BR]) as CountBR,
sum([SP]) as CountSP,
sum([IFs]) as CountIFs,
sum([PO]) as CountPO,
sum([MB]) as CountMB,
sum([DS]) as CountDS,
sum([DSS]) as CountDSS,
sum([ISs]) as CountISS,
sum([WD]) as CountWD,
sum([LT]) as CountLT,
sum([SRA]) as CountSRA,
sum([SRB]) as CountSRB,
sum([SRC]) as CountSRC
from CallActivity
group by [AccountCallId]
select
[AccountCallId],
sum(Cast([SS] as int)) as CountSS,
sum(Cast([ST] as int)) as CountST,
sum(Cast([SA] as int)) as CountSA,
sum(Cast([OC] as int)) as CountOC,
sum(Cast([EC] as int)) as CountEC,
sum(Cast([BR] as int)) as CountBR,
sum(Cast([SP] as int)) as CountSP,
sum(Cast([IFs] as int)) as CountIFs,
sum(Cast([PO] as int)) as CountPO,
sum(Cast([MB] as int)) as CountMB,
sum(Cast([DS] as int)) as CountDS,
sum(Cast([DSS] as int)) as CountDSS,
sum(Cast([ISs] as int)) as CountISS,
sum(Cast([WD] as int)) as CountWD,
sum(Cast([LT] as int)) as CountLT,
sum(Cast([SRA] as int)) as CountSRA,
sum(Cast([SRB] as int)) as CountSRB,
sum(Cast([SRC] as int)) as CountSRC
from CallActivity
group by [AccountCallId]
gslakmal
Member
118 Points
425 Posts
How to count boolean values
Apr 15, 2012 05:33 PM|LINK
HI,
Here is my table
Same AccountcallId can have many records, I want create query to display AccountcallId and count of true values of each bit fields
How can i do that
gimimex
Participant
1052 Points
157 Posts
Re: How to count boolean values
Apr 15, 2012 06:02 PM|LINK
Hi,
Try:
select [AccountCallId], sum([SS]) as CountSS, sum([ST]) as CountST, sum([SA]) as CountSA, sum([OC]) as CountOC, sum([EC]) as CountEC, sum([BR]) as CountBR, sum([SP]) as CountSP, sum([IFs]) as CountIFs, sum([PO]) as CountPO, sum([MB]) as CountMB, sum([DS]) as CountDS, sum([DSS]) as CountDSS, sum([ISs]) as CountISS, sum([WD]) as CountWD, sum([LT]) as CountLT, sum([SRA]) as CountSRA, sum([SRB]) as CountSRB, sum([SRC]) as CountSRC from CallActivity group by [AccountCallId]I hope this helps.
gslakmal
Member
118 Points
425 Posts
Re: How to count boolean values
Apr 15, 2012 06:17 PM|LINK
I try that, i getting this error
Msg 8117, Level 16, State 1, Line 3
Operand data type bit is invalid for sum operator.
gimimex
Participant
1052 Points
157 Posts
Re: How to count boolean values
Apr 15, 2012 06:28 PM|LINK
Try:
select [AccountCallId], sum(Cast([SS] as int)) as CountSS, sum(Cast([ST] as int)) as CountST, sum(Cast([SA] as int)) as CountSA, sum(Cast([OC] as int)) as CountOC, sum(Cast([EC] as int)) as CountEC, sum(Cast([BR] as int)) as CountBR, sum(Cast([SP] as int)) as CountSP, sum(Cast([IFs] as int)) as CountIFs, sum(Cast([PO] as int)) as CountPO, sum(Cast([MB] as int)) as CountMB, sum(Cast([DS] as int)) as CountDS, sum(Cast([DSS] as int)) as CountDSS, sum(Cast([ISs] as int)) as CountISS, sum(Cast([WD] as int)) as CountWD, sum(Cast([LT] as int)) as CountLT, sum(Cast([SRA] as int)) as CountSRA, sum(Cast([SRB] as int)) as CountSRB, sum(Cast([SRC] as int)) as CountSRC from CallActivity group by [AccountCallId]I hope this helps.