MySQL查询以计算总列的百分比 [英] MySQL query to calculate percentage of total column

问题描述

如何转换此结果:

Group     |  Sum
Services  | 11120.99
Vendas    | 3738.00

进入:

Group     |  Sum
Services  | 74.84
Vendas    | 25.16

即，秒显示结果占总数的百分比.

That is, the second displays the results as percentages of total.

这是我尝试过的:

SELECT categories.cat AS 'Group', SUM(atual) AS 'Sum'
FROM `table1` INNER JOIN
     categories
     ON table1.category_id=categories.id
GROUP BY categoria

推荐答案

您可以将未分组或未拆分的总和保留，然后将其除以总和查询.这样，您只需一次总选择即可获得更快的运行时间

you can left join a total sum that is not grouped or split up, and divide that by your sum query. this way you are just doing the total select once for faster runtime

SELECT cat, sum_atual, sum_atual/total_atual as percent_atual 
FROM
(   SELECT categories.cat AS cat, SUM(atual) AS sum_atual
    FROM `table1` 
    JOIN categories ON table1.category_id=categories.id
    GROUP BY categoria
) t
LEFT JOIN 
(   SELECT SUM(atual) as total_atual
    FROM `table1`
) t1

这篇关于MySQL查询以计算总列的百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！