mysql - how do I use the same table in an inner and outer query?

Question

consider the following table

id	class	Y
1	A	20
1	B	50
1	C	30
1	TOTAL	100

I need to generate a column that is the percentage each class contributes to the effort

something like

select Y/(select Y from table where class = 'TOTAL') 
from table 
group by id

but how do I pass the id to the inner query?

Answer 1

With aliases you will get something like:

select 
   t1.Y/(select t2.Y from table1 t2 where t2.class = 'TOTAL') 
from table1 t1
group by t1.id

But this will produce the following error (see: DBFIDDLE:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_1321975282.t1.Y' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I think this is what you meant to do, see DBFIDDLE:

select 
   t1.Y/t2.sumY
from table1 t1
cross join (select sum(y) as sumY from table1 where class='TOTAL') t2

In this query t1,t2 and sumY are aliases.

output:

t1.Y/t2.sumY
0.2000
0.5000
0.3000
1.0000