mysql group by value in multiple columns

Question

    code1 | code2 | code3 | code4 | code5
row1    A       B       C
row2    B       A       D        E
row3   F       C

How can I group this table with count of value in codeX column So

A: 2
   B: 2
   C: 2
   D: 1
   E: 1
   F: 1

Gordon Linoff · Accepted Answer · 2015-02-10 13:19:03Z

1

If I understand correctly, you want a list of the columns where something appears. You can unpivot the table an use group_concat:

select code, count(*) as cnt, group_concat(which)
from (select code1 as code, 'code1' as which from table union all
      select code2, 'code2' as which from table union all
      select code3, 'code3' as which from table union all
      select code4, 'code4' as which from table
     ) c
where code is not null
group by code;

I used the full column names instead of #1 -- it seems more useful. But you can put in #1 etc for which.

edited Feb 10, 2015 at 13:19

answered Feb 10, 2015 at 12:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

tugce Over a year ago

Well I wanna group with the value inside codeX, so not only for something. I want to have result for all values in codeX

tugce Over a year ago

actually, I only need count of appearance, any ideas?

Gordon Linoff Over a year ago

@tugce . . . Just use count(*).

tugce · Accepted Answer · 2015-02-10 13:22:00Z

0

SELECT productCode, count(*) AS count
FROM
(
    SELECT Code1 AS productCode FROM table
    UNION ALL
    SELECT Code2 FROM table
     UNION ALL
    SELECT Code3 FROM table
     UNION ALL
    SELECT Code4 FROM table
     UNION ALL
    SELECT Code5 FROM table

) AS codes
GROUP BY productCode
ORDER BY count DESC

answered Feb 10, 2015 at 13:22

tugce

6713 gold badges13 silver badges28 bronze badges

Collectives™ on Stack Overflow

mysql group by value in multiple columns

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related