Clever Geek Handbook

Count how many columns have a specific value

I have a table that looks like this:

ID    x1    x2    x3    x4
1     20    30    0     0
2     60    0     0     0
3     10    30    0     0
4     30    30    30    30

I want to be able to query this and return an id with the number of columns greater than 0 as their value in this row. Thus, the result will look like this:

ID    Count
1     2
2     1
3     2
4     4
+3


source to share


3 answers


Try the following:

SELECT ID, z.cnt
FROM mytable
CROSS APPLY (SELECT COUNT(*) AS cnt 
             FROM (VALUES (x1), (x2), (x3), (x4)) x(y)
             WHERE x.y > 0) z

This query uses the Table Value Designer to create a row in a row whose rows are the columns of the original table. By doing COUNT

on this inline table, you can get the number of columns greater than zero.



I think it scales well if you have more than 4 columns.

Demo here

+5


source


Try the following:

Select 
    ID,
    Case When x1 <> 0 Then 1 Else 0 End + 
    Case When x2 <> 0 Then 1 Else 0 End + 
    Case When x3 <> 0 Then 1 Else 0 End + 
    Case When x4 <> 0 Then 1 Else 0 End as Count
From MyTable


As long as this is easy to code, the more columns you have, the more your choice will be the more columns you will need to add.

+5


source


This should do what you want if you have a fixed set of columns known at design time:

 SELECT ID,
        IIF (x1 > 0, 1, 0) + 
        IIF (x2 > 0, 1, 0) + 
        IIF (x3 > 0, 1, 0) + 
        IIF (x4 > 0, 1, 0) 
 FROM MyTable
+1


source






More articles:


All Articles