MySQL Group By values that are equal over multiple columns

Question

Starting with an example:

For this data:

NAME    Col1  Col2  Col3
row1    0     2     4
row2    1     5     0
row3    1     1     0

Desired query output is:

ColValue CountInCol1 CountInCol2 CountInCol3
0        1           0           2
1        2           1           0
2        0           1           0
4        0           0           1
5        0           1           0

The question explained:

If columns share common values, how can returned rows from a query be condensed on multiple columns? In this example, how can Col1, Col2, and Col3 be grouped in to the same ColValue result?

score 1 · Accepted Answer · answered Nov 08 '14 at 12:29

1

I would do this by unpivoting the data and then repivoting it:

select colvalue,
       sum(which = 'col1') as CountInCol1,
       sum(which = 'col2') as CountInCol2,
       sum(which = 'col3') as CountInCol3
from (select 'col1' as which, col1 as colvalue from data union all
      select 'col2', col2 from data union all
      select 'col3', col3 from data
     ) d
group by colvalue;

answered Nov 08 '14 at 12:29

Gordon Linoff

1,242,037
58
646
786

Thanks for pointing me in the right direction! I'm still wrapping my head around intermediary tables like this and all the problems they can solve. – Nov 09 '14 at 01:32

MySQL Group By values that are equal over multiple columns

Starting with an example:

The question explained:

1 Answers1

Linked