如何使用 SQL 语句计算百分比
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/770579/
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
How to calculate percentage with a SQL statement
提问by Alex
I have a SQL Server table that contains users & their grades. For simplicity's sake, lets just say there are 2 columns - name& grade. So a typical row would be Name: "John Doe", Grade:"A".
我有一个包含用户及其成绩的 SQL Server 表。为简单起见，我们只说有 2 列 - name& grade。所以典型的行是姓名：“John Doe”，等级：“A”。
I'm looking for one SQL statement that will find the percentages of all possible answers. (A, B, C, etc...) Also, is there a way to do this without defining all possible answers (open text field - users could enter 'pass/fail', 'none', etc...)
我正在寻找一个可以找到所有可能答案的百分比的 SQL 语句。（A、B、C 等...）此外，有没有一种方法可以在不定义所有可能答案的情况下执行此操作（打开文本字段 - 用户可以输入“通过/失败”、“无”等...）
The final output I'm looking for is A: 5%, B: 15%, C: 40%, etc...
我正在寻找的最终输出是 A: 5%, B: 15%, C: 40%, 等等...
回答by Alex Aza
The most efficient (using over()).
select Grade, count(*) * 100.0 / sum(count(*)) over() from MyTable group by GradeUniversal (any SQL version).
select Grade, count(*) * 100.0 / (select count(*) from MyTable) from MyTable group by Grade;With CTE, the least efficient.
with t(Grade, GradeCount) as ( select Grade, count(*) from MyTable group by Grade ) select Grade, GradeCount * 100.0/(select sum(GradeCount) from t) from t;
最有效的（使用 over()）。
select Grade, count(*) * 100.0 / sum(count(*)) over() from MyTable group by Grade通用（任何 SQL 版本）。
select Grade, count(*) * 100.0 / (select count(*) from MyTable) from MyTable group by Grade;使用 CTE，效率最低。
with t(Grade, GradeCount) as ( select Grade, count(*) from MyTable group by Grade ) select Grade, GradeCount * 100.0/(select sum(GradeCount) from t) from t;
回答by Jason
I have tested the following and this does work. The answer by gordyii was close but had the multiplication of 100 in the wrong place and had some missing parenthesis.
我已经测试了以下内容，这确实有效。gordyii 的答案很接近，但在错误的地方乘以 100 并且缺少一些括号。
Select Grade, (Count(Grade)* 100 / (Select Count(*) From MyTable)) as Score
From MyTable
Group By Grade
回答by John Gibb
Instead of using a separate CTE to get the total, you can use a window function without the "partition by" clause.
您可以使用不带“partition by”子句的窗口函数，而不是使用单独的 CTE 来获取总数。
If you are using:
如果您正在使用：
count(*)
to get the count for a group, you can use:
要获取组的计数，您可以使用：
sum(count(*)) over ()
to get the total count.
得到总数。
For example:
例如：
select Grade, 100. * count(*) / sum(count(*)) over ()
from table
group by Grade;
It tends to be faster in my experience, but I think it might internally use a temp table in some cases (I've seen "Worktable" when running with "set statistics io on").
根据我的经验，它往往会更快，但我认为在某些情况下它可能会在内部使用临时表（我在使用“set statistics io on”运行时看到了“Worktable”）。
EDIT:I'm not sure if my example query is what you are looking for, I was just illustrating how the windowing functions work.
编辑：我不确定我的示例查询是否是您要查找的内容，我只是在说明窗口函数的工作原理。
回答by Jhonny D. Cano -Leftware-
You have to calculate the total of grades If it is SQL 2005 you can use CTE
你必须计算总成绩 如果是 SQL 2005 你可以使用 CTE
WITH Tot(Total) (
SELECT COUNT(*) FROM table
)
SELECT Grade, COUNT(*) / Total * 100
--, CONVERT(VARCHAR, COUNT(*) / Total * 100) + '%' -- With percentage sign
--, CONVERT(VARCHAR, ROUND(COUNT(*) / Total * 100, -2)) + '%' -- With Round
FROM table
GROUP BY Grade
回答by Jeremy
You need to group on the grade field. This query should give you what your looking for in pretty much any database.
您需要在成绩字段上分组。这个查询应该给你你在几乎任何数据库中寻找的东西。
Select Grade, CountofGrade / sum(CountofGrade) *100
from
(
Select Grade, Count(*) as CountofGrade
From Grades
Group By Grade) as sub
Group by Grade
You should specify the system you're using.
您应该指定您正在使用的系统。
回答by Fandango68
I simply use this when ever I need to work out a percentage..
我只是在需要计算百分比时使用它。
ROUND(CAST((Numerator * 100.0 / Denominator) AS FLOAT), 2) AS Percentage
Note that 100.0 returns decimals, whereas 100 on it's own will round up the result to the nearest whole number, even with the ROUND() function!
请注意，100.0 返回小数，而 100 本身会将结果四舍五入到最接近的整数，即使使用 ROUND() 函数！
回答by GordyII
The following should work
以下应该工作
ID - Key
Grade - A,B,C,D...
EDIT: Moved the * 100and added the 1.0to ensure that it doesn't do integer division
编辑：移动* 100并添加了1.0以确保它不进行整数除法
Select
Grade, Count(ID) * 100.0 / ((Select Count(ID) From MyTable) * 1.0)
From MyTable
Group By Grade
回答by Jonathan Leffler
This is, I believe, a general solution, though I tested it using IBM Informix Dynamic Server 11.50.FC3. The following query:
我相信这是一个通用的解决方案，尽管我使用 IBM Informix Dynamic Server 11.50.FC3 对其进行了测试。以下查询：
SELECT grade,
ROUND(100.0 * grade_sum / (SELECT COUNT(*) FROM grades), 2) AS pct_of_grades
FROM (SELECT grade, COUNT(*) AS grade_sum
FROM grades
GROUP BY grade
)
ORDER BY grade;
gives the following output on the test data shown below the horizontal rule. The ROUNDfunction may be DBMS-specific, but the rest (probably) is not. (Note that I changed 100 to 100.0 to ensure that the calculation occurs using non-integer - DECIMAL, NUMERIC - arithmetic; see the comments, and thanks to Thunder.)
在水平规则下方显示的测试数据上给出以下输出。该ROUND函数可能是特定于 DBMS 的，但其余的（可能）不是。（请注意，我将 100 更改为 100.0 以确保使用非整数进行计算 - DECIMAL、NUMERIC - 算术；请参阅评论，并感谢 Thunder。）
grade pct_of_grades
CHAR(1) DECIMAL(32,2)
A 32.26
B 16.13
C 12.90
D 12.90
E 9.68
F 16.13
CREATE TABLE grades
(
id VARCHAR(10) NOT NULL,
grade CHAR(1) NOT NULL CHECK (grade MATCHES '[ABCDEF]')
);
INSERT INTO grades VALUES('1001', 'A');
INSERT INTO grades VALUES('1002', 'B');
INSERT INTO grades VALUES('1003', 'F');
INSERT INTO grades VALUES('1004', 'C');
INSERT INTO grades VALUES('1005', 'D');
INSERT INTO grades VALUES('1006', 'A');
INSERT INTO grades VALUES('1007', 'F');
INSERT INTO grades VALUES('1008', 'C');
INSERT INTO grades VALUES('1009', 'A');
INSERT INTO grades VALUES('1010', 'E');
INSERT INTO grades VALUES('1001', 'A');
INSERT INTO grades VALUES('1012', 'F');
INSERT INTO grades VALUES('1013', 'D');
INSERT INTO grades VALUES('1014', 'B');
INSERT INTO grades VALUES('1015', 'E');
INSERT INTO grades VALUES('1016', 'A');
INSERT INTO grades VALUES('1017', 'F');
INSERT INTO grades VALUES('1018', 'B');
INSERT INTO grades VALUES('1019', 'C');
INSERT INTO grades VALUES('1020', 'A');
INSERT INTO grades VALUES('1021', 'A');
INSERT INTO grades VALUES('1022', 'E');
INSERT INTO grades VALUES('1023', 'D');
INSERT INTO grades VALUES('1024', 'B');
INSERT INTO grades VALUES('1025', 'A');
INSERT INTO grades VALUES('1026', 'A');
INSERT INTO grades VALUES('1027', 'D');
INSERT INTO grades VALUES('1028', 'B');
INSERT INTO grades VALUES('1029', 'A');
INSERT INTO grades VALUES('1030', 'C');
INSERT INTO grades VALUES('1031', 'F');
回答by Aakashi
SELECT Grade, GradeCount / SUM(GradeCount)
FROM (SELECT Grade, COUNT(*) As GradeCount
FROM myTable
GROUP BY Grade) Grades
回答by Steve Willcock
In any sql server version you could use a variable for the total of all grades like this:
在任何 sql server 版本中，您都可以使用一个变量来表示所有成绩的总和，如下所示：
declare @countOfAll decimal(18, 4)
select @countOfAll = COUNT(*) from Grades
select
Grade, COUNT(*) / @countOfAll * 100
from Grades
group by Grade
