sql - 如何使用SQL语句计算百分比

我有一个 SQL Server 表,其中包含用户的&等级。 为了简单起见,我们只说有 2列- name & grade 。 所以典型的一行就是名字: "John doe"。等级:""。

我正在寻找一个SQL语句,它可以找到所有可能答案的百分比。 (a、b、c 等......)另外,有没有办法做这没有定义所有可能的答案( 打开文本字段- 用户可以输入'通过/失败'。'无',等......)

我要寻找的最终输出是 A: 5%,B: 15%,C: 40%,等等 。

时间:

我已经测试过以下内容,这确实。 答案是gordyii,但在错误的地方有 100 的乘法,并且有一些缺失括号。


Select Grade, (Count(Grade)* 100 / (Select Count(*) from MyTable)) as Score
From MyTable
Group by Grade

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

    
    select Grade, count(*) * 100.0 / sum(count(*)) over()
    from MyTable
    group by Grade
    
    
  2. 通用( 任何SQL版本) 。

    
    select Rate, count(*) * 100.0 / (select count(*) from MyTable)
    from MyTable
    group by Rate;
    
    
  3. 使用cte,最有效的。

    
    with t(Rate, RateCount) as ( select Rate, count(*) from MyTable group by Rate
    )
    select Rate, RateCount * 100. 0/(select sum(RateCount) from t)
    from t;
    
    

而不是使用一个单独的CTE来获取总无由""分区子句,可以使用开窗函数。

如果你使用:


count(*)

要获取组的计数,可以使用:


sum(count(*)) over ()

获取总数。

例如:


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

在"),它倾向于更快以我的经验,但是我想它可能在内部使用临时表在某些情况下( 我见过"工作台与""当运行set statistics io.

编辑: 我不确定我的示例查询是否是你所需要的,我只是在说明窗口函数是如何工作的。

你需要在成绩场上分组。 这个查询应该能让你在几乎任何数据库中找到你想要的东西。


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

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

如果是SQL 2005,则必须计算等级总数


 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

下面的工作应该


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

在任何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 以确保计算使用非整数- 十进制数字,数字-,请参见评论,并感谢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');


SELECT Grade, GradeCount / SUM(GradeCount)
FROM (SELECT Grade, COUNT(*) as GradeCount from myTable GROUP by Grade) Grades

你可以在查询中使用一个选择( 未测试的,也不能确定哪个更快):


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

你也可以使用存储过程( 对火鸟语法致歉):


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

...