本文是小编为大家收集整理的关于用于计算总数百分比的MySQL查询的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
如何转换此结果:
Group | Sum Services | 11120.99 Vendas | 3738.00
进入:
Group | Sum Services | 74.84 Vendas | 25.16
也就是说，第二个显示结果为总数.
这是我尝试的:
SELECT categories.cat AS 'Group', SUM(atual) AS 'Sum'
FROM `table1` INNER JOIN
categories
ON table1.category_id=categories.id
GROUP BY categoria
推荐答案
您可以剩下的总和未分组或拆分，然后除以您的总和.这样，您只是在进行总数一次，以使运行时间更快
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
其他推荐答案
SELECT categories.cat AS categoria,
SUM(atual) * 100 / (select sum(atual) from table1) AS percentages
FROM `table1`
INNER JOIN categories ON table1.category_id=categories.id
GROUP BY categoria
其他推荐答案
您可以做几种方法.一种是仅在select子句中使用子查询.如下所示，假设table1中的category_id列始终匹配categories:
SELECT c.categoria AS "Group", SUM(t1.atual) AS "Sum",
SUM(t1.atual) / (SELECT SUM(t1.atual) FROM table1) as "Percent"
FROM `table1` t1 INNER JOIN
categories c
ON t1.category_id = c.id
GROUP BY c.categoria;
我也更改了group by子句.对于group by和select来说，使用相同的列是一个好主意.我在所有列参考中添加了表别名，这是另一个好练习.
问题描述
How to convert this result:
Group | Sum Services | 11120.99 Vendas | 3738.00
Into:
Group | Sum Services | 74.84 Vendas | 25.16
That is, the second displays the results as percentages of total.
This is what I tried:
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
其他推荐答案
SELECT categories.cat AS categoria,
SUM(atual) * 100 / (select sum(atual) from table1) AS percentages
FROM `table1`
INNER JOIN categories ON table1.category_id=categories.id
GROUP BY categoria
其他推荐答案
You can do this several ways. One is to just use a subquery in the select clause. As written below, this assumes that the category_id column in table1 always matches categories:
SELECT c.categoria AS "Group", SUM(t1.atual) AS "Sum",
SUM(t1.atual) / (SELECT SUM(t1.atual) FROM table1) as "Percent"
FROM `table1` t1 INNER JOIN
categories c
ON t1.category_id = c.id
GROUP BY c.categoria;
I changed the group by clause as well. It is a good idea for the group by and select to use the same columns. And I added table aliases to all the column references, another good practice.
更多相关问答
联系站长