Counting a 'bit' field - 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 a 'bit' field

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-02-14 : 10:35:13

I have a field in a sql table which is type bit (true or false)

Here's my view

SELECT TOP (100) PERCENT ACC_Cause_C, COUNT(ACC_Cause_C) AS No_Incidents, SUM(ACC_NoDaysLost_B) AS [No days lost],
ACC_Riddor_B AS [RIDDOR Reportable], ACC_PersonClassification_A
FROM dbo.tblACC_AccidentReport
GROUP BY ACC_Cause_C, ACC_PersonClassification_A, ACC_Riddor_B
HAVING (ACC_PersonClassification_A = N'PCC Employee') AND (ACC_Riddor_B = 1)
ORDER BY ACC_Cause_C, [RIDDOR Reportable]

Against each ACC_Cause_C I want to count the number of ACC_Riiddor_B that are true. How can I do this ?

end result wil be like this

Cause No Incidents No dayslost RIDDOR Reportable
Running 8 10 3
Walking 6 2 4

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-14 : 10:48:35

SUM(CASE WHEN ACC_Riddor_B = 1 THEN 1 ELSE 0 END) as [RIDDOR Reportable],

Jim

Everyday I learn something that somebody else already knew

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-02-14 : 10:50:23

SUM(CAST ACC_Riddor_B AS INT)) AS RIDDOR Reportable

--
Gail Shaw
SQL Server MVP

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-02-14 : 11:09:18

Thanks :-)

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-02-15 : 04:20:17

My result is now showing as this

Cause No Incidents No dayslost RIDDOR Reportable
Running 8 10 3
Running 2 1 0
Walking 6 2 4

Is there a way I can show Running as one line eg
Running 10 11 3

here's my View

SELECT TOP (100) PERCENT ACC_Cause_C, COUNT(ACC_Cause_C) AS No_Incidents, SUM(ACC_NoDaysLost_B) AS [No days lost],
SUM(CASE WHEN ACC_Riddor_B = 1 THEN 1 ELSE 0 END) AS [RIDDOR Reportable]
FROM dbo.tblACC_AccidentReport
GROUP BY ACC_Cause_C, ACC_PersonClassification_A, ACC_Riddor_B
HAVING (ACC_PersonClassification_A = N'PCC Employee')
ORDER BY ACC_Cause_C

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-02-15 : 04:41:11

I've sorted it by basing another view on that view

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources