CODEWITHSUNDEEP
t-sql
Arek
Arek

Reputation: 3

T_SQL counting particular values in one row with multiple columns

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.

Upvotes: 0

Views: 24

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

I'm going to strongly suggest that you abandon your current table design, and instead store one day per month, per record, not column. That is, use this design:

ID   | Date       | Value
5000 | 2021-01-01 | NULL
5000 | 2021-01-02 | NULL
5000 | 2021-01-03 | 1
5000 | 2021-01-04 | 1
5000 | 2021-01-05 | NULL
...
5000 | 2021-01-31 | 5

Then use this query:

SELECT
    ID,
    CONVERT(varchar(7), Date, 120),
    COUNT(CASE WHEN Value = 1 THEN 1 END) AS one_cnt
FROM yourTable
GROUP BY
    ID,
    CONVERT(varchar(7), Date, 120);

Upvotes: 1

Related Questions