counting number of rows while skipping some based on columns

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 sh开发者_StackOverflow中文版ould have been NULLS.

Thanks!

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.

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

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.

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

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