Count all data in table

Question

I am trying to count all my errors but cant figure out the best way of doing this. I have 25 columns of different errors anything that is not null is an error. I want to count everything that is not null ending with one total for each dept sql code based on 2 columns of errors is below. any advice on this would be great many thanks in advance

SELECT 
office,([Agg_Error]+[cover_Error]) Total_Errors
FROM(SELECT Placing_Office OFFICE,
COUNT([Aggregates_Error])[Agg_Error],
COUNT([Basisofcover_Error])[cover_Error]
FROM [LEE].[dbo].[ERRORS]
GROUP BY Placing_Office)A

Answer 1

0

Are the error columns numeric?

If not, try something like

select 
 Placing_Office as Office,
 sum(case when [Aggregates_Error] is not null then 1 else 0 end) as [Agg_Error],
 sum(case when [Basisofcover_Error] is not null then 1 else 0 end) as [Cover_Error]
from
 [LEE].[dbo].[ERRORS]
group by Placing_Office

as the inner query

answered Nov 21 '12 at 12:09 PM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

(comments are locked)

Answer 2

0

I would recommend sticking with COUNT, as it will filter out the NULL values automatically. If you don't need any additional joins, you could do this without a subquery.

SELECT
Placing_Office,
  COUNT([Aggregates_Error]) +
  COUNT([Basisofcover_Error]) AS TotalErrors
FROM [LEE].[dbo].[ERRORS]
GROUP BY Placing_Office

This way you wouldn't need to add/remove columns in two places if you needed to make a change.

answered Nov 21 '12 at 09:36 PM

Beandon10
138 ● 3

(comments are locked)

Count all data in table

Follow this question

Related Questions