x

Count all data in table

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
more ▼

asked Nov 21, 2012 at 12:00 PM in Default

n3w2sql gravatar image

n3w2sql
850 14 22 33

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Nov 21, 2012 at 12:09 PM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Nov 21, 2012 at 09:36 PM

Beandon10 gravatar image

Beandon10
136 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834

asked: Nov 21, 2012 at 12:00 PM

Seen: 543 times

Last Updated: Nov 23, 2012 at 01:45 AM