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

### 本文来自：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 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;
```

### 问题描述

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.