SQL Count occurrences of multiple columns



I want to count occurrences of Number 3 from multiple columns with group by Primary Key. I have a table like this.

enter image description here

And I have tried with this.

enter image description here

But my output is

enter image description here

But expected output is something like this

enter image description here

Answer

Your query only counts rows with multiple threes one time.

You could use a union:

select  id
,       sum(case when val = 3 then 1 else 0 end)
from    (
        select id, s1 as val from t1
        union all select id, s2 from t1
        union all select id, s3 from t1
        union all select id, s4 from t1
        union all select id, s5 from t1
        ) sub
group by
        id

Example at db-fiddle.com



Source: stackoverflow