我有一个SQL Server表,包含用户和他们的成绩。为了简单起见,我们只说有两列——姓名和年级。所以一个典型的行是:名字:“John Doe”,等级:“a”。
我正在寻找一个SQL语句,将找到所有可能的答案的百分比。(A, B, C,等等…)此外,有没有一种方法可以在不定义所有可能答案的情况下做到这一点(打开文本字段-用户可以输入“通过/失败”,“none”等…)
我想要的最终输出是A: 5%, B: 15%, C: 40%等等……
我有一个SQL Server表,包含用户和他们的成绩。为了简单起见,我们只说有两列——姓名和年级。所以一个典型的行是:名字:“John Doe”,等级:“a”。
我正在寻找一个SQL语句,将找到所有可能的答案的百分比。(A, B, C,等等…)此外,有没有一种方法可以在不定义所有可能答案的情况下做到这一点(打开文本字段-用户可以输入“通过/失败”,“none”等…)
我想要的最终输出是A: 5%, B: 15%, C: 40%等等……
当前回答
你可以在你的from查询中使用子选择(未经测试,不确定哪个更快):
SELECT Grade, COUNT(*) / TotalRows
FROM (SELECT Grade, COUNT(*) As TotalRows
FROM myTable) Grades
GROUP BY Grade, TotalRows
Or
SELECT Grade, SUM(PartialCount)
FROM (SELECT Grade, 1/COUNT(*) AS PartialCount
FROM myTable) Grades
GROUP BY Grade
Or
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
其他回答
你可以在你的from查询中使用子选择(未经测试,不确定哪个更快):
SELECT Grade, COUNT(*) / TotalRows
FROM (SELECT Grade, COUNT(*) As TotalRows
FROM myTable) Grades
GROUP BY Grade, TotalRows
Or
SELECT Grade, SUM(PartialCount)
FROM (SELECT Grade, 1/COUNT(*) AS PartialCount
FROM myTable) Grades
GROUP BY Grade
Or
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
在任何sql server版本中,您都可以使用一个变量来表示所有等级的总和,如下所示:
declare @countOfAll decimal(18, 4)
select @countOfAll = COUNT(*) from Grades
select
Grade, COUNT(*) / @countOfAll * 100
from Grades
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');
您可以使用没有“partition by”子句的窗口函数,而不是使用单独的CTE来获得总数。
如果你正在使用:
count(*)
要获得一个组的计数,您可以使用:
sum(count(*)) over ()
来获得总数。
例如:
select Grade, 100. * count(*) / sum(count(*)) over ()
from table
group by Grade;
根据我的经验,它往往更快,但我认为在某些情况下它可能在内部使用临时表(我在运行“set statistics io on”时看到过“Worktable”)。
编辑: 我不确定我的示例查询是否是你要找的,我只是说明了窗口函数是如何工作的。
以下操作应该可以工作
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