Mysql merge count multiple column

Question

I have a table(tb_data) which like this

+---------+---------------------+---------------------+---------------------+---------------------+ 
| Disease | Additional_Disease1 | Additional_Disease2 | Additional_Disease3 | Additional_Disease4 |
+---------+---------------------+---------------------+---------------------+---------------------+ 
| A01     | A03                 | A03                 |                     |                     |
| A03     | A02                 |                     |                     |                     |
| A03     | A05                 |                     |                     |                     |
| A03     | A05                 |                     |                     |                     |
| A02     | A05                 | A01                 | A03                 |                     | 
+---------+---------------------+---------------------+---------------------+---------------------+

My question is how to make it like this

+---------+-------+ 
| Disease | Total |
+---------+-------+
| A03     | 6     |
| A05     | 3     |
| A01     | 2     |
| A02     | 2     |
+---------+-------+

And oh here's my failed attempt

select Disease, 
count(Disease + Additional_Disease1 + Additional_Disease2 + Additional_Disease3 + Additional Disease_4) as Total
from tb_data
group by Disease
order by Disease desc

I've also tried this but it didn't work, it says "Unknown column 'Disease' in 'field list' as i really don't understand what was wrong

GMB · Accepted Answer · 2020-03-03 15:49:23Z

2

You can use union all to unpivot your dataset, and then aggregation:

select disease, count(*) total
from (
    select disease from mytable
    union all select additional_disease1 from mytable
    union all select additional_disease2 from mytable
    union all select additional_disease3 from mytable
    union all select additional_disease4 from mytable
) t
group by disease
order by total desc, disease

answered Mar 3, 2020 at 15:49

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Mysql merge count multiple column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related