Aggregate function for Boolean

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Aggregate function for Boolean

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-27 : 18:41:17

Aggregate function for Boolean

I have a table that flags rows with a Boolean. I need to count the number of rows with a 1 and the number of rows with a 0. The following SELECT counts properly but I don’t know what aggregate function to use so I can tell which count is for the 1’s and which is for the 0’s.

What function should I use? Or should I be doing this a different way?

select count(*), sum(UniqueName) from MyTable group by UniqueName

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-27 : 19:28:14

Does this work for you?


declare @t table (rowid int identity(1,1), b bit)
insert @t (b)
select 1 union all select 1 union all select 0 union all select 0 union all select 0

select * from @t
select b, count(*) bCount from @t group by b
output:

--select * from @t
rowid       b    
----------- ---- 
1           1
2           1
3           0
4           0
5           0

--select b, count(*) bCount from @t group by b
b    bCount      
---- ----------- 
0    3
1    2

Be One with the Optimizer
TG

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-27 : 20:23:05

That will do it. thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources