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

本文是小编为大家收集整理的关于用于计算总数百分比的MySQL查询的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

本文来自:IT宝库(https://www.itbaoku.cn)

如何转换此结果:

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来说,使用相同的列是一个好主意.我在所有列参考中添加了表别名,这是另一个好练习.

本文地址:https://www.itbaoku.cn/post/1897578.html

问题描述

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.