MySQL uses the generated column in the selected query

Question

I have a MySQL query that performs a brief operation (summing up the counts in a select expression), and I want to use the result to perform a mathematical operation, but I get 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 that the table does not have a real “common” column. How can I make the request work?

Answer 1

You can save totalas variable , and then use this in the calculation of separation.

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

NOTE. GROUPis a reserved word in MySQL. You must enclose it (and all other field / table names) in backticks (`).

Answer 2

:

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

:

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

Answer 3

group mysql, table, :

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

For More Information: MySQL Reserved Words

You will see what you can really use count, but I would still put all the table and column names in quotation marks.

Answer 4

Your problem is that the internal query should generate 1 result for each row, and not 1 for each group. You want to add a where clause in your inner query saying something like

where inner_table.group = outer_table.group

to return only one result.