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

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

如果你正在使用:

count(*)

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

sum(count(*)) over ()

来获得总数。

例如:

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

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

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

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

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

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

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

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

这个在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;