MySQL use generated column in select query

Question

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 1

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