CODEWITHSUNDEEP
mysqlsql
user3477312
user3477312

Reputation:

MySQL Group By values that are equal over multiple columns

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?

Upvotes: 0

Views: 711

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

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;

Upvotes: 1

Related Questions