Sql Query to find Counts

Question

I have a table as below .

Id Product1 Product2 Product3
1     1      null     null
2     1       2       null
3     3       1       null
4     2       3        1  

Now I would like to write a query to count the entries with Product 1. In the above case it is 2 for Product1 , 1 for Product2 and 1 for Product 3

I came up with a query something like this.

select
Count(Product1) +
Count(Product2) +
Count(Product3)
from  
Table 
where Product1  =  1

But it gives me an inaccurate result. Is there a way I can get the number of occurances for each product ?

Group By

Desired output would be something like this :

ProductID Product1Count Product2Count Product3Count 
  1           2              1             1                    
  2           1              1             0
  3           1              1             0 

Answer 1

select Count(CASE WHEN Product1 = 1 THEN 1 END) +
       Count(CASE WHEN Product2 = 1 THEN 1 END) +
       Count(CASE WHEN Product3 = 1 THEN 1 END)
from   Table1 
where  1 IN (Product1, Product2, Product3)

• SQLFIddle Demo

The reason for adding this line: where 1 IN (Product1, Product2, Product3) is to count only on the affected rows, thus making it more faster than running through all records.

Answer 2

Something like this would work:

SELECT
SUM(CASE WHEN Product1 = 1 THEN 1 ELSE 0 END)
+ SUM(CASE WHEN Product2 = 1 THEN 1 ELSE 0 END)
+ SUM(CASE WHEN Product3 = 1 THEN 1 ELSE 0 END)
FROM Table