MySQL use generated column in select query

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


You can also do this without introducing a variable:

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

Produces:

+------+-------+-------+---------+
| id   | count | total | percent |
+------+-------+-------+---------+
|    1 |     3 |     5 |  0.6000 |
|    2 |     2 |     5 |  0.4000 |
+------+-------+-------+---------+
2 rows in set (0.05 sec)

Tags:

Mysql

Php