CODEWITHSUNDEEP
sql-servert-sql
Ian Warburton
Ian Warburton

Reputation: 15676

Average of groups is incorrect

My query is like this...

SELECT
   ClientId,
   AVG(Rate)
FROM
  Clients
GROUP BY
   ClientId
WITH ROLLUP

This gives...

NULL, 17.5769
1, 16.75
2, 17.50
3, 18.50
4, 17.50
5, 18.50

That's the wrong average. It should be 17.75.

What's wrong with this query?

Upvotes: 2

Views: 285

Answers (2)

iamdave
iamdave

Reputation: 12243

You are misunderstanding how averages are calculated. If you were to calculate an avg of the group averages you would see the 17.75 you are expecting:

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;

Output

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

Because you are using with rollup it is (correctly) calculating the average of the entire dataset for your total value, which is not the same as calculating an average of the group averages.

Upvotes: 5

Thom A
Thom A

Reputation: 95554

The Average of an Average is not the same as a Average of all values. Take the following:

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;

This gives the results below:

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

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

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

Notice that the final 2 results are not the same. You are after the value in the 3rd dataset, however, the aggragation you are doing is the second.

Upvotes: 3

Related Questions