counting number of rows while skipping some based on columns

Question

I have a table in SQL Server 2008 that looks kind of like this:

ID   I1  I2 ...  IN
-------------------------
1    2   3  ..... 2
2    0   0  ..... 0 
3    2   1  ..... 5

Where IN is about 9 columns. What I need to do is count the number of rows, but skipping rows where the values of I1..IN are 0's. I'm new to SQL and I have basically something like this:

SELECT COUNT(ID) AS Expr1, 
       COUNT(I1) AS Expr2, 
       COUNT(I2) AS Expr3, 
       COUNT(IN) AS ExprN 
 FROM [mytable] 
WHERE (Expr2 !=0) 
  AND (Expr3 != 0) 
  AND (ExprN != 0)

I imagine there is an easier and more efficient way of doing this? I need to ensure that all of the column entries are 0 (other than the ID). I would prefer not to rely on a single column being 0 or not to make the determination. I working with a database somebody already created and these 0's should have been NULLS.

Thanks!

Answer 1

Simply select count(*) from [mytable] where (i1<>0 and ... iN<>0)

Answer 2

Perhaps something like this

SELECT COUNT(ID) AS CountOfRowsGreaterThanZero
FROM [mytable]
WHERE (I1>0) AND .... AND (IN>0)

As you're counting rows you shouldn't need to count each column as the number should be the same.

Answer 3

If, as you mention, using NULLs solves your problem (because COUNT(X) only counts non-NULL values), then wrap your 0s with NULLIF

Answer 4

SELECT COUNT(ID) AS Expr1, 
       COUNT(I1) AS Expr2, 
       COUNT(I2) AS Expr3, 
       COUNT(IN) AS ExprN 
 FROM [mytable] 
WHERE (I1 + I2 + I3 + I4 + [... +] IN) <> 0

... but I'm still not sure this meets the problem you're trying to solve. COUNT(I1) will return the same as COUNT(I2) because they'll be non-NULL in all the rows. Are you trying to find the number of rows with non-zero values?

SELECT COUNT(ID) AS Expr1,
       SUM(CASE WHEN I1 = 0 THEN 0 ELSE 1 END) as Expr2,
       SUM(CASE WHEN I2 = 0 THEN 0 ELSE 1 END) as Expr3,
       SUM(CASE WHEN IN = 0 THEN 0 ELSE 1 END) as ExprN,
 FROM [mytable] 
WHERE (I1 + I2 + I3 + I4 + [... +] IN) <> 0

Answer 5

You could create a view on [mytable] which replaces the zeroes with nulls, and use that view for all future selects. It would help you gradually transition the zero-to-null assumption into your code.

CREATE VIEW [myview]
AS
SELECT [ID],
    NULLIF(I1, 0) I1,
    NULLIF(I2, 0) I2,
    NULLIF([IN], 0) [IN]
FROM [mytable]

The question is, though, do you want to count the number of non-zero values in each column, or the number of rows with all zeros?

SELECT COUNT(COALESCE(ID,I1,I2,[IN])) AS Expr1, 
       COUNT(I1) AS Expr2, 
       COUNT(I2) AS Expr3, 
       COUNT([IN]) AS ExprN 
 FROM [myview]

Expr1 will count the number of rows where all values are 0 or null, Expr2 will count the zeroes and nulls in I1, etc. Note that in this case, each of the resulting columns can have different values.