我有一个SQL Server表,包含用户和他们的成绩。为了简单起见,我们只说有两列——姓名和年级。所以一个典型的行是:名字:“John Doe”,等级:“a”。

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

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


当前回答

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

选择Grade, (Count(Grade)* 1.0 / (Select Count(*) From MyTable))作为来自MyTable Group By Grade的分数

其他回答

这个在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;
SELECT Grade, GradeCount / SUM(GradeCount)
FROM (SELECT Grade, COUNT(*) As GradeCount
      FROM myTable
      GROUP BY Grade) Grades

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

Select Grade, (Count(Grade)* 100 / (Select Count(*) From MyTable)) as Score
From MyTable
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

你可以在你的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