I have little problem with counting cells with particular value in one row in MSSMS.
Table looks like
ID | Month | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 11 | 12 | 13 | 14 | 15 | 16 | ... | 31 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
5000 | 1 | null | null | 1 | 1 | null | 1 | 1 | null | null | 2 | 2 | 2 | 2 | 2 | null | null | 3 | 3 | 3 | 3 | 3 | null | ... | 1 |
I need to count how many cells in one row have value for example 1. In this case it would be 5. Data represents worker shifts in a month. Be aware that there is a column named month (FK with values 1-12), i don't want to count that in a result. Column ID is ALWAYS 4 digit number.
Possibility is to use count(case when) but in examples there are only two or three columns not 31. Statement will be very long. Is there any other option to count it?
Thanks for any advices.