Mysql Unknown column in where clause union all

Question

Still related to my Previous Question, have a table(tb_data) which like this

+---------+---------------------+---------------------+---------------------+---------------------+-------+ 
| Disease | Additional_Disease1 | Additional_Disease2 | Additional_Disease3 | Additional_Disease4 | Room  |
+---------+---------------------+---------------------+---------------------+---------------------+-------+
| A01     | A03                 | A02                 |                     |                     | Man   |
| A03     | A02                 |                     |                     |                     | Woman |
| A03     | A05                 |                     |                     |                     | Child |
| A03     | A05                 |                     |                     |                     | Man   |
| A02     | A05                 | A01                 | A03                 |                     | UGD   |
+---------+---------------------+---------------------+---------------------+---------------------+-------+

My question is how to make it like this

+---------+-------+ 
| Disease | Total |
+---------+-------+
| A03     | 2     |
| A02     | 1     |
| A01     | 1     |
| A05     | 1     |
+---------+-------+

Here's my code attempt

    select Disease, count(*) total
    from (
    select Disease from tb_data
    union all select Additional_Disease1 from tb_data
    union all select Additional_Disease2 from tb_data
    union all select Additional_Disease3 from tb_data
    union all select Additional_Disease4 from tb_data
) t
where Disease is not Null
and Room = 'Man'
group by Disease
order by total desc, Disease

Which result the error

Error Code: 1054. Unknown column 'Room' in 'where clause'

Any time you find yourself with enumerated column names (above, say '2') you should consider whether your schema design is really optimal — Strawberry, Mar 07 '20 at 08:35
optimal? can you elaborate/give case example? i kinda new with programming so i still don't know much... — Kanien Notiros, Mar 07 '20 at 09:01
Your design should look similar to the output of Nick's subquery — Strawberry, Mar 07 '20 at 09:06

score 3 · Accepted Answer · answered Mar 07 '20 at 08:12

Your issue is that your derived table doesn't include the Room column. You can either filter by Room in the derived table:

select Disease, count(*) total
from (
    select Disease from tb_data where Room = 'Man'
    union all select Additional_Disease1 from tb_data where Room = 'Man'
    union all select Additional_Disease2 from tb_data where Room = 'Man'
    union all select Additional_Disease3 from tb_data where Room = 'Man'
    union all select Additional_Disease4 from tb_data where Room = 'Man'
) t
where Disease is not Null
group by Disease
order by total desc, Disease

Or include the Room column in the derived table:

select Disease, count(*) total
from (
    select Room, Disease from tb_data
    union all select Room, Additional_Disease1 from tb_data
    union all select Room, Additional_Disease2 from tb_data
    union all select Room, Additional_Disease3 from tb_data
    union all select Room, Additional_Disease4 from tb_data
) t
where Disease is not Null
and Room = 'Man'
group by Disease
order by total desc, Disease

score 0 · Answer 2 · answered Mar 07 '20 at 13:11

If tb_data is large or generated by a subquery, then I don't recommend the union all approach for unpivoting it. Instead:

select (case n.n
           when 1 then Disease
           when 2 then Additional_Disease1
           when 3 then Additional_Disease2
           when 4 then Additional_Disease3
           when 5 then Additional_Disease4
        end) as the_disease, count(*)
from tb_data d cross join
     (select 1 as n union all
      select 2 as n union all
      select 3 as n union all
      select 4 as n union all
      select 5 as n
     ) n
where Room = 'Man'
group by the_disease
having the_disease is not null

Mysql Unknown column in where clause union all

2 Answers2