获取列总和并用于计算占总数的百分比 (mySQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30509149/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get column sum and use to calculate percent of total (mySQL)
提问by jonmrich
Here is a very basic look at my table. I have columns 1 and 2 and need to generate column 3. Column 3 is simply the total of the Numbercolumn for all Namedivided by Numberfor the given row.
这是对我的表的非常基本的了解。我有第 1 列和第 2 列，需要生成第 3 列。第 3 列只是给定行除以Number所有列的总和。 NameNumber
| Name | Number | % of total |
| ------------- |:-------------:| -----: |
| Bob | 5 | 25 |
| Sally | 10 | 50 |
| John | 5 | 25 |
I'm struggling with how to get the total of the number row and use this as a value to calculate the rest.
我正在努力如何获得数字行的总数并将其用作计算其余部分的值。
EDIT: I'm looking to do this as a single query instead of two separate if possible.
编辑：如果可能的话，我希望将其作为单个查询而不是两个单独的查询来执行。
回答by Giorgos Betsos
回答by AdamMc331
If I were doing this, I would start by storing a variable that held the total, like this:
如果我这样做，我将首先存储一个包含总数的变量，如下所示：
SET @total := (SELECT SUM(number) FROM myTable);
Once I had that variable, I could run a query that got the percentage for each row like this:
一旦我有了那个变量，我就可以运行一个查询来获取每一行的百分比，如下所示：
SELECT name, number, (number / @total) * 100 AS percentage
FROM myTable;
If you don't want to use a variable, you can just move that subquery into your select statement:
如果您不想使用变量，您可以将该子查询移动到您的 select 语句中：
SELECT name, number, (number / (SELECT SUM(number) FROM myTable)) * 100 AS percentage
FROM myTable;
Here is an SQL Fiddleexample with each approach.
这是每种方法的SQL Fiddle示例。
回答by flavi
You can get it with a subselect:
您可以通过子选择获得它：
SELECT Name, Number, Number * 100 / (select sum(Number) FROM MYTABLE) AS '% of total'
FROM mytable
