# Averages don't add: re-combining grouped reads

> A chart over a pre-aggregated table can average the averages and draw a wrong number. How Emolysis re-combines grouped reads exactly, and where that stops.

A revenue dashboard reads a gold table of orders. The table is too large to copy, so someone pre-aggregates it on the platform: one row per region and day, with the day's revenue and its average order value. The chart then shows average order value by region, and the chart tool does what it was asked: it takes the average of the `avg_revenue` column. The number looks plausible, passes review and is wrong. An average of averages weights every day the same, whether that day had two orders or two thousand.

This post walks through the arithmetic, the SQL that gets it right, and how Emolysis handles the same problem when a chart reads a gold table that is too large to copy whole.

## A worked example

Here is a grouped read of an `orders` table: one row per region and day, every order counted.

```sql
SELECT
  region,
  order_date,
  SUM(revenue)   AS revenue_sum,
  COUNT(revenue) AS revenue_count,
  MIN(revenue)   AS revenue_min,
  MAX(revenue)   AS revenue_max
FROM sales.orders
GROUP BY region, order_date;
```

| region | order_date | revenue_sum | revenue_count | revenue_min | revenue_max |
| ------ | ---------- | ----------: | ------------: | ----------: | ----------: |
| eu     | 2026-10-01 |         100 |             2 |          40 |          60 |
| eu     | 2026-10-02 |         260 |             5 |          31 |          80 |
| us     | 2026-10-01 |         340 |             8 |          20 |          75 |

The chart wants one bar per region. The tempting query divides each row first and averages the results:

```sql
SELECT region, AVG(revenue_sum / revenue_count) AS avg_order
FROM daily_orders
GROUP BY region;
```

| region | avg_order |
| ------ | --------: |
| eu     |     51.00 |
| us     |     42.50 |

For eu that is (50 + 52) / 2 = 51. But eu had seven orders worth 360 in total, so the average order is 360 / 7 = 51.43. The error here is small because the two days are similar. Make one day a single large order and the gap grows as large as you like.

*Diagram: Two grouped rows for the region eu: sum 100, count 2, average 50; and sum 260, count 5, average 52. Averaging the averages gives 51, which is wrong. Summing the sums and the counts gives 360 over 7, 51.43, which is the average of all seven orders.*

## Partials that combine exactly

The fix is to carry, for each measure, the pieces that still combine correctly after grouping, and to combine those pieces again at the chart's grain:

- a **sum** combines as the sum of the sums;
- a **count** combines as the sum of the counts, never as a count of rows;
- a **minimum** combines as the least minimum, a **maximum** as the greatest maximum;
- an **average** is carried as a sum and a count, and combines as the sum of the sums divided by the sum of the counts.

```sql
SELECT
  region,
  SUM(revenue_sum)                                   AS revenue,
  SUM(revenue_count)                                 AS orders,
  MIN(revenue_min)                                   AS smallest_order,
  MAX(revenue_max)                                   AS largest_order,
  SUM(revenue_sum) / NULLIF(SUM(revenue_count), 0)   AS avg_order
FROM daily_orders
GROUP BY region;
```

| region | revenue | orders | smallest_order | largest_order | avg_order |
| ------ | ------: | -----: | -------------: | ------------: | --------: |
| eu     |     360 |      7 |             31 |            80 |     51.43 |
| us     |     340 |      8 |             20 |            75 |     42.50 |

Two details matter. The count beside an average is `COUNT(revenue)`, the number of non-null values, because that is what `AVG(revenue)` divides by; `COUNT(*)` gives a different answer as soon as a revenue is missing. And `NULLIF` keeps a group with no values from dividing by zero; its average is empty, as `AVG` over no values would be.

On the platforms here, `/` divides exactly; on an engine where dividing integers truncates, such as PostgreSQL, cast one side to a decimal first.

## How Emolysis does it

A dashboard chart in Emolysis moves from illustrative rows to a sample and then to live rows, read from the deployed gold table. When the gold table fits within a size limit, Emolysis copies it whole into the project's local lakehouse and the chart's query reads that copy. When it does not, Emolysis reads it grouped instead: one query on the platform's compute, over every row, grouped by the chart's dimensions (its x and series fields), with each measure as the partials above. Live still means every row of the gold table counted.

The chart's own query then combines the partials as the chart draws them, with the rules in the list. The chart spec also says that its rows are already combined, and names the column each measure reads; an average reads its sum and its count. A renderer that draws such a spec combines rows by the same rules and never applies the chart's aggregate a second time. The spec is checked before anything draws it, so an average without its count is refused as malformed rather than drawn from the wrong column. The [chart spec](https://emolysis.com/docs/chart-types) carries that instruction with the rows, so how to read them travels with them.

## Where it stops

Grouping works because every aggregate a chart can use (sum, count, minimum, maximum and average) has partials that combine exactly. Many useful aggregates do not. A distinct count cannot be rebuilt from per-day distinct counts: a customer who ordered on both days is counted twice. A median cannot be rebuilt from per-day medians at all. Emolysis does not approximate them. An aggregate without an exact partial is not read grouped, and Emolysis says why.

Some charts cannot be grouped either. A table or a scatter plot that shows its rows as they are needs the rows, so grouping would change what it shows; over the size limit, such a chart stays on its sample and Emolysis says why. So does a chart whose measure is also its dimension. In both cases you can narrow the gold table, change the chart, or keep the sample until the table is smaller.

Grouping also has a cost. The grouped read runs on your platform's compute over the whole gold table, each time the chart goes live again. A gold table that is already at the chart's grain is the cheaper design, and the [Aggregation / gold](https://emolysis.com/shapes/aggregation-gold) shape builds exactly that. The grouped read is the safety net for the tables that are not.

The rule fits on one line: carry sums and counts, never averages, and combine them again where the chart draws. See the [glossary entry on grain](https://emolysis.com/glossary/grain) for why the grain of a table decides which numbers it can answer.

> Emolysis is in private development. Nothing described here is released yet.

---

Page: https://emolysis.com/news/averages-dont-add. Emolysis is a product of Bandpey GmbH, Munich, Germany. It is in private development, and nothing is released yet.