如何用SQL语句计算百分比

我有一个SQL Server表，其中包含用户&他们的成绩。为了简单起见，假设有2列——name &grade。所以一个典型的行是:名字:“John Doe”，等级:“a”。

我正在寻找一个SQL语句，将找到所有可能的答案的百分比。(A, B, C，等等…)此外，有没有一种方法可以在不定义所有可能答案的情况下做到这一点(打开文本字段-用户可以输入“通过/失败”，“none”等…)

我想要的最终输出是A: 5%， B: 15%， C: 40%等等……

1214847 次浏览
小开

你必须计算总分 如果是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
小开

你们需要在成绩领域分组。这个查询应该为您提供在几乎任何数据库中要查找的内容。

    Select Grade, CountofGrade / sum(CountofGrade) *100
from
(
Select Grade, Count(*) as CountofGrade
From Grades
Group By Grade) as sub
Group by Grade

您应该指定您正在使用的系统。

小开

在任何sql server版本中，您都可以使用一个变量来表示所有等级的总和，如下所示:

declare @countOfAll decimal(18, 4)
select @countOfAll = COUNT(*) from Grades


select
Grade,  COUNT(*) / @countOfAll * 100
from Grades
group by Grade
小开

你可以在你的from查询中使用子选择(未经测试，不确定哪个更快):

SELECT Grade, COUNT(*) / TotalRows
FROM (SELECT Grade, COUNT(*) As TotalRows
FROM myTable) Grades
GROUP BY Grade, TotalRows

或

SELECT Grade, SUM(PartialCount)
FROM (SELECT Grade, 1/COUNT(*) AS PartialCount
FROM myTable) Grades
GROUP BY Grade

或

SELECT Grade, GradeCount / SUM(GradeCount)
FROM (SELECT Grade, COUNT(*) As GradeCount
FROM myTable
GROUP BY Grade) Grades

你也可以使用存储过程(Firebird语法不好意思):

SELECT COUNT(*)
FROM myTable
INTO :TotalCount;


FOR SELECT Grade, COUNT(*)
FROM myTable
GROUP BY Grade
INTO :Grade, :GradeCount
DO
BEGIN
Percent = :GradeCount / :TotalCount;
SUSPEND;
END
小开

以下操作应该可以工作

ID - Key
Grade - A,B,C,D...

编辑:移动* 100并添加1.0以确保它不做整数除法

Select
Grade, Count(ID) * 100.0 / ((Select Count(ID) From MyTable) * 1.0)
From MyTable
Group By Grade
小开

我认为这是一种通用的解决方案，不过我使用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;

给出关于水平规则下面显示的测试数据的以下输出。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');
小开
最佳答案

我已经测试了以下内容，这确实有效。戈迪伊的答案很接近，但在错误的位置上乘了100，并且缺少了一些括号。

Select Grade, (Count(Grade)* 100 / (Select Count(*) From MyTable)) as Score
From MyTable
Group By Grade
小开

您可以使用没有“partition by”子句的窗口函数，而不是使用单独的CTE来获得总数。

如果你正在使用:

count(*)

要获得一个组的计数，您可以使用:

sum(count(*)) over ()

来获得总数。

例如:

select Grade, 100. * count(*) / sum(count(*)) over ()
from table
group by Grade;

根据我的经验，它往往更快，但我认为在某些情况下它可能在内部使用临时表(我在运行“set statistics io on”时看到过“Worktable”)。

<强>编辑: 我不确定我的示例查询是否是你要找的，我只是说明了窗口函数是如何工作的

小开

  1. 最有效的(使用over())。

    select Grade, count(*) * 100.0 / sum(count(*)) over()
from MyTable
group by Grade

  2. Universal (any SQL version).

    select Grade, count(*) * 100.0 / (select count(*) from MyTable)
from MyTable
group by Grade;

  3. 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;
小开 
SELECT Grade, GradeCount / SUM(GradeCount)
FROM (SELECT Grade, COUNT(*) As GradeCount
FROM myTable
GROUP BY Grade) Grades
小开

当我需要计算一个百分比时，我简单地使用这个。

ROUND(CAST((Numerator * 100.0 / Denominator) AS FLOAT), 2) AS Percentage

请注意，100.0返回一个小数，而100本身将把结果四舍五入到最接近的整数，即使使用round(…，2)函数!

小开

我也遇到过类似的问题。您应该能够得到乘以1.0而不是100的正确结果。参见附图示例

Select Grade， (Count(Grade)* 1.0 / (Select Count(*) From MyTable)) as Score From MyTable Group By Grade See reference image attached

.
小开

这个在MS SQL中工作得很好。它将varchar转换为两个小数限制浮点数的结果。

Select field1, cast(Try_convert(float,(Count(field2)* 100) /
Try_convert(float, (Select Count(*) From table1))) as decimal(10,2)) as new_field_name
From table1
Group By field1, field2;

提交答案