MechEngineer MechEngineer - 1 year ago 111
MySQL Question

MySQL use generated column in select query

I have a MySQL query that runs a brief operation (totalling the counts in a select statement) and I want to use the result to do a math operation, but I'm getting an error.


id | group | count |
1 1 3
2 1 2


select id, count,
(select sum(count) from table group by group) as total,
count/total as percent
from table

The error is because there is no real "total" column in the table. How can I make the query work?

Answer Source

You can save total as a variable, then use that in the division calculation.

SELECT `id`, `count`, @total:=(SELECT sum(`count`) FROM `table` GROUP BY `group`) AS `total`, `count`/@total AS `percent` FROM `table`

NOTE: GROUP is a reserved word in MySQL. You should enclose it (and all other field/table names) in backticks (`).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download