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

avatar image

n3w2sql
870 22 32 39

(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

avatar image

Kev Riley ♦♦
63.8k 48 61 81

(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

avatar image

Beandon10
136 2 4

(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.

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:

x2072

asked: Nov 21, 2012 at 12:00 PM

Seen: 719 times

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

Copyright 2016 Redgate Software. Privacy Policy