MechEngineer MechEngineer - 3 months ago 13
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.

Table:

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


Query:

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

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 (`).

Comments