你误解了平均数的计算方法。如果你要计算一个 avg 小组平均数 你会看到 17.75 您期望:

select ClientID
        ,avg(Rate) as AvgRate
from (values(1, 16.75)
           ,(2, 17.50)
           ,(3, 18.50)
           ,(4, 17.50)
           ,(5, 18.50)
    ) as r(ClientID, Rate)
group by ClientID
with rollup;

输出

+----------+-----------+
| ClientID |  AvgRate  |
+----------+-----------+
| 1        | 16.750000 |
| 2        | 17.500000 |
| 3        | 18.500000 |
| 4        | 17.500000 |
| 5        | 18.500000 |
| NULL     | 17.750000 |
+----------+-----------+

因为你正在使用 with rollup 它(正确地)计算了 整个数据集 为了你的总价值,

平均值的平均值与所有值的平均值不同。采取以下行动:

SELECT a,
       AVG(b) AS AvB
FROM (VALUES (1, 5.0),
             (1, 7.0),
             (2, 9.0),
             (3, 4.0),
             (3, 5.0),
             (4, 6.0)) V (a, b)
GROUP BY a;

SELECT AVG(b) AS AvAll
FROM (VALUES (1, 5.0),
             (1, 7.0),
             (2, 9.0),
             (3, 4.0),
             (3, 5.0),
             (4, 6.0)) V (a, b);

WITH CTE AS(
    SELECT a,
           AVG(b) AS AvB
    FROM (VALUES (1, 5.0),
                 (1, 7.0),
                 (2, 9.0),
                 (3, 4.0),
                 (3, 5.0),
                 (4, 6.0)) V (a, b)
    GROUP BY a)
SELECT AVG(AvB) AS AvgOfAvg
FROM CTE;

结果如下:

a           AvB
----------- ---------------------------------------
1           6.000000
2           9.000000
3           4.500000
4           6.000000

AvAll
---------------------------------------
6.000000

AvgOfAvg
---------------------------------------
6.375000

请注意,最后两个结果并不相同。您在第三个数据集中查找值,但是,您正在执行的加总是第二个。