T_SQL counting particular values in one row with multiple columns

Question

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.

Take a look at this: stackoverflow.com/questions/15745042/… — sbgib
– sbgib, Commented Feb 18, 2021 at 8:41

Tim Biegeleisen · Accepted Answer · 2021-02-18 08:44:04Z

1

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);

answered Feb 18, 2021 at 8:44

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arek Over a year ago

Thanks, tables are rebuilded and it looks like its suites to other part of DB.

Collectives™ on Stack Overflow

T_SQL counting particular values in one row with multiple columns

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related