How to tabulate from three columns into a single column with a reference table including there totals?

user_14365803 0
Viewed 43

I am using MySQL 8.0 Here's my table:

create table sampledata (
id int,
age int,
seeing int,
hearing int,
walking int,
PRIMARY KEY (id)
);

INSERT INTO `sampledata` VALUES (1,64,1,1,1),(2,63,2,2,1),(3,32,3,2,1),(4,39,4,2,1),(5,11,5,2,2),(6,3,NULL,NULL,NULL),(7,30,NULL,2,2),(8,7,2,2,2);

create table difficulty (
id int,
code int,
description varchar(50),
PRIMARY KEY (id)
);

INSERT INTO difficulty VALUES (1,1,'No, No Difficulty'),(2,2,'Yes, Some Difficulty'),(3,3,'Yes, A Lot of Difficulty'),(4,4,'Cannot do it at all');

Expected Output:

functional difficulty table

The description of each value from the seeing, hearing, walking column in sample data table will come from the difficulty table. However, there are some values that are not included in the difficulty table. "Out of Range" means that there is a value in sample data table but not in the difficulty table. "Valid Blank" means that difficulty table is NULL and age in sample data table is less than 5. "Invalid Blank" means that difficulty table is NULL and age in sample data table is 5 or more.

This is kinda tricky. I hope you can halp me with this.

mysql
1 Answers

If I understood correctly then it is case of unpivot with joining and aggregation. It's not an ideal solution but it may resolve your problem:

select 
t3.category,
if(grouping(t3.description) ,
'Total', t3.description) as description,
sum(t4.count_) as count from 
( 
select c2.category, c1.description 
from (
select  description from difficulty union all select 'Valid Blank' 
union all select 'NOT VALID' 
union all select 'InValid Blank')c1 
cross join 
(
select 'seeing' as category 
union all select 'hearing' 
union all select 'walking') c2 
) 
t3

left join

(select 
t5.category,
t5.description,
count(*) as count_
from 
(select 
t2.category, 
case 
when t2.value is null and t2.age<5 then 'Valid Blank'
when t2.value is not null and t1.code is null  then 'NOT VALID'
when t1.code is null and t2.age>5 then 'InValid Blank'
else t1.description
end description
from difficulty t1 right join 
(
select 'seeing' as category, age, seeing as value from sampledata
union all
select 'hearing', age,hearing from sampledata
union all 
select 'walking', age,walking from sampledata
)t2 on t1.code=t2.value
) t5
group by t5.category,
t5.description
) t4
  on 
t3.category=t4.category and t3.description=t4.description

group by t3.category,
t3.description with rollup

DEMO

user_77724830
Related