Record Count - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Record Count

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2013-11-22 : 14:21:53

Hi

I'm using the code below to count the number of errors for a given record. Each Score_ field is a bit column and a value of 0 counts as an error. This works fine but I would also like to calculate the total number of records with one or more errors (not the total number of errors)


SELECT 
	COUNT(CASE WHEN Score_CorrectID = 0 Then Score_CorrectID END) As Score_CorrectID_Errors,
	COUNT(CASE WHEN Score_MiniMiranda = 0 Then Score_MiniMiranda END) As Score_MiniMiranda_Errors,
	COUNT(CASE WHEN Score_Accuracy = 0 Then Score_Accuracy END) As Score_Accuracy_Errors,
	COUNT(CASE WHEN Score_Notepad = 0 Then Score_Notepad END) As Score_Notepad_Errors,
	COUNT(CASE WHEN Score_Tone = 0 Then Score_Tone END) As Score_Tone_Errors,
	COUNT(CASE WHEN Score_PCAResponsive = 0 Then Score_PCAResponsive END) As Score_PCAResponsive_Errors
  FROM
	Calls

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-11-22 : 14:33:27

[code]

SELECT
SUM(CASE WHEN Score_CorrectID = 0 Then 1 Else 0 END) As Score_CorrectID_Errors,
SUM(CASE WHEN Score_MiniMiranda = 0 Then 1 Else 0 END) As Score_MiniMiranda_Errors,
SUM(CASE WHEN Score_Accuracy = 0 Then 1 Else 0 END) As Score_Accuracy_Errors,
SUM(CASE WHEN Score_Notepad = 0 Then 1 Else 0 END) As Score_Notepad_Errors,
SUM(CASE WHEN Score_Tone = 0 Then 1 Else 0 END) As Score_Tone_Errors,
SUM(CASE WHEN Score_PCAResponsive = 0 Then 1 Else 0 END) As Score_PCAResponsive_Errors,
SUM(CASE WHEN Score_CorrectID = 0 OR
Score_MiniMiranda = 0 OR
Score_Accuracy = 0 OR
Score_Notepad = 0 OR
Score_Tone = 0 OR
Score_PCAResponsive = 0 Then 1 Else 0 END) AS Score_AnyErrors
FROM
Calls

[/code]

evanburen
Posting Yak Master

167 Posts

Posted - 2013-11-22 : 14:42:11

Works great. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources