When aggregate tables are used and AVG is calculated among measures, it is calculated incorrectly.
The SQL query looks like this :
sum(`id`) as `m0`,
sum(`measure_avg`) / sum(`fact_count`) as `m1`,
sum(`measure_sum`) as `m2`
Indeed, sum of averages divided by sum of number of facts is not equal to average.
Possible solution: always calculate sum in aggregate tables instead of avg, using
INSERT INTO agg_l_101_something (
COUNT AS `id`,
SUM(`measure`) AS `measure_avg`, /* NOW AVG */
SUM(`measure`) AS `measure_sum`,
COUNT AS `fact_count`
in this case the result will be correct.
NOTE: created 2 new cases (
MONDRIAN-2398, MONDRIAN-2399) to cover variations of bad behavior with averages in aggregate tables. We've focused this case specifically on the issue of average values from aggregate tables being mishandled when a query hits the lowest granularity of the table. NULL handling and issues with aggregation method selection are covered under the other 2 cases.