I'm looking for the best possible solution to what first seemed easy to do but here I am...
trying to count nr of bit values (no null) in my db based in companyID. Problem is there are 4 bit columns so I want the procedure or view to count them in one go. is this possible. here is what I mean...
select
CoID,
SUM(case when col1 = '1' then 1 else 0 end) as col1,
SUM(case when col2 = '1' then 1 else 0 end) as col2,
SUM(case when col3 = '1' then 1 else 0 end) as col3,
SUM(case when col4 = '1' then 1 else 0 end) as col4
from MyTable
group by CoID
ok, had to restart sql for some reason or other, so columns are being recognized but the return given are all 1's. I tested 2 records with the same companyID and various col1-4 have true indicicated..? any ideas?
select
CoID,
SUM(case when col1 = '1' then 1 else 0 end) as col1,
SUM(case when col2 = '1' then 1 else 0 end) as col2,
SUM(case when col3 = '1' then 1 else 0 end) as col3,
SUM(case when col4 = '1' then 1 else 0 end) as col4
from MyTable
where CoID = 1
group by CoID
or
select
SUM(case when col1 = '1' then 1 else 0 end) as col1,
SUM(case when col2 = '1' then 1 else 0 end) as col2,
SUM(case when col3 = '1' then 1 else 0 end) as col3,
SUM(case when col4 = '1' then 1 else 0 end) as col4
from MyTable
where CoID = 1
dolittle
Member
157 Points
153 Posts
SQL Counting multiple bit columns returned in one row
Jul 15, 2012 08:42 PM|LINK
Hi guys,
I'm looking for the best possible solution to what first seemed easy to do but here I am...
trying to count nr of bit values (no null) in my db based in companyID. Problem is there are 4 bit columns so I want the procedure or view to count them in one go. is this possible. here is what I mean...
CoID col1 col2 col3 col4
1 1 1 NULL 1
1 NULL 1 1 NULL
3 NULL 1 NULL 1
-----------------------------------------
Based on CoID = 1 the result should be
RETURN...
CoID col1 col2 col3 col4
1 1 2 1 1
any help would be much appreciated
Shuvo Aymon
Contributor
4820 Points
1167 Posts
Re: SQL Counting multiple bit columns returned in one row
Jul 15, 2012 08:49 PM|LINK
select top 1 col1,(select top 1 col2 from TableName where coID=1 col2 is not null) col2,
(select top 1 col3 from TableName where coID=1 col3 is not null) col3,
(select top 1 col4 from TableName where coID=1 col4 is not null) col4,
from TableName where coID=1 and col1 is not null
above query may serve you the result you expected. but there should be any better option. will try later.
If more posts give you useful answers, Please mark each post as "Answer".
imapsp
Member
134 Points
22 Posts
Re: SQL Counting multiple bit columns returned in one row
Jul 15, 2012 09:05 PM|LINK
Hi,
Try:
select CoID, SUM(case when col1 = '1' then 1 else 0 end) as col1, SUM(case when col2 = '1' then 1 else 0 end) as col2, SUM(case when col3 = '1' then 1 else 0 end) as col3, SUM(case when col4 = '1' then 1 else 0 end) as col4 from MyTable group by CoIDHope this helps.
dolittle
Member
157 Points
153 Posts
Re: SQL Counting multiple bit columns returned in one row
Jul 15, 2012 09:13 PM|LINK
hmm. Getting invalid Columns errors. checked everything, seems to be okay, might I be missing something here?
dolittle
Member
157 Points
153 Posts
Re: SQL Counting multiple bit columns returned in one row
Jul 15, 2012 09:19 PM|LINK
ok, had to restart sql for some reason or other, so columns are being recognized but the return given are all 1's. I tested 2 records with the same companyID and various col1-4 have true indicicated..? any ideas?
dolittle
Member
157 Points
153 Posts
Re: SQL Counting multiple bit columns returned in one row
Jul 15, 2012 09:23 PM|LINK
thanks, somewhat cleaner and it worked off the bat. cheers to all answers
dolittle
Member
157 Points
153 Posts
Re: SQL Counting multiple bit columns returned in one row
Jul 15, 2012 09:28 PM|LINK
imapsp, sorry just hit the answer button to fast.... but i think its almost there...
how would i instead of group by have the counts added where CoID = 1 ?
imapsp
Member
134 Points
22 Posts
Re: SQL Counting multiple bit columns returned in one row
Jul 15, 2012 09:31 PM|LINK
Try:
select CoID, SUM(case when col1 = '1' then 1 else 0 end) as col1, SUM(case when col2 = '1' then 1 else 0 end) as col2, SUM(case when col3 = '1' then 1 else 0 end) as col3, SUM(case when col4 = '1' then 1 else 0 end) as col4 from MyTable where CoID = 1 group by CoIDor
select SUM(case when col1 = '1' then 1 else 0 end) as col1, SUM(case when col2 = '1' then 1 else 0 end) as col2, SUM(case when col3 = '1' then 1 else 0 end) as col3, SUM(case when col4 = '1' then 1 else 0 end) as col4 from MyTable where CoID = 1Hope this helps.
dolittle
Member
157 Points
153 Posts
Re: SQL Counting multiple bit columns returned in one row
Jul 15, 2012 09:42 PM|LINK
Perfect. Many Thanks!