CODEWITHSUNDEEP
mysqlsqldatabasegroup-bycount
Ada S
Ada S

Reputation: 419

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

Upvotes: 0

Views: 592

Answers (4)

Md. Fazlur Rahman
Md. Fazlur Rahman

Reputation: 84

SELECT id,  ( SUM(CASE WHEN s1 =3 THEN  1 ELSE  0 END ) +    SUM(CASE WHEN s2 =3 THEN  1 ELSE  0 END ) +
    SUM(CASE WHEN s3 =3 THEN  1 ELSE  0 END ) +
    SUM(CASE WHEN s4 =3 THEN  1 ELSE  0 END ) +
    SUM(CASE WHEN s5 =3 THEN  1 ELSE  0 END )     ) AS val FROM t1 GROUP BY id

I think it will be helpful for you

Upvotes: 0

Andomar
Andomar

Reputation: 238086

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

Upvotes: 1

Kiran Patil
Kiran Patil

Reputation: 339

Try Below Query..

select id,(count(s1)+count(s2)+count(s3)+count(s4)+count(s5))valcount from(
select id, case when s1=3  then 1 end as s1, 
case when s2=3  then 1 end as s2,
case when s3=3  then 1 end as s3,
case when s4=3  then 1 end as s4,
case when s5=3  then 1 end as s5 
from test) group by id

and Try another way

select id,
count(decode(s1,3,1))+
count(decode(s2,3,1))+
count(decode(s3,3,1))+
count(decode(s4,3,1))+
count(decode(s5,3,1))valcount
from test
group by id

Upvotes: 1

forpas
forpas

Reputation: 164099

With this:

select id,
  (s1 = 3) + (s2 = 3) + (s3 = 3) + (s4 = 3) + (s5 = 3) valcount
from tablename

Each of the boolean expressions:

s? = 3

evaluates to 0 or 1.

Upvotes: 2

Related Questions