Counting Bit Types - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Counting Bit Types

Author

Topic

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-04-23 : 10:13:26

I have a table that I want to use for reports that holds these datatypes

safe: Integer
unsafe: Integer
made_safe: Bit
unobserved: Bit

Which gets populated.

I want to count the values in each field but Bit fields seem to be awkward. This is my attempt

SELECT
SUM(safe)as TotalSafe,
SUM(unsafe)as TotalUnsafe,
COUNT(Case WHEN unobserved = 1 THEN 1 ELSE NULL END)as NotSeen,
COUNT(Case when made_safe = 1 THEN 1 ELSE NULL END) as TotalMadeSafe

FROM myTable

Any ideas please

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-23 : 10:17:56

[code]SELECT
SUM(safe)as TotalSafe,
SUM(unsafe)as TotalUnsafe,
SUM(Case WHEN unobserved = 1 THEN 1 ELSE 0 END)as NotSeen,
SUM(Case when made_safe = 1 THEN 1 ELSE 0 END) as TotalMadeSafe
FROM myTable[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-04-23 : 10:25:56

Thank You

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-23 : 10:53:45

Some more possibilities...

select sum(unobserved*1) from myTable
select sum(cast(unobserved as tinyint)) from myTable
select sum(case when unobserved = 1 then 1 else 0 end) from myTable
select sum(case when unobserved = 1 then 1 end) from myTable
select count(case when unobserved = 1 then 1 end) from myTable

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources