Double Counting in Aggregation
Double counting in aggregation is what happens when a value is added to a total more than once because the rows carrying it were multiplied by a join. The join is usually correct and the aggregate is usually correct; what is wrong is that they were combined without accounting for the change in grain.
Where the extra rows come from
An inner join returns every combination of rows that satisfies the condition. If one order has three shipments, the order row appears three times in the result — once per matching shipment. Nothing about that is a mistake; it is what join cardinality means. But the order amount, which was one value per order, is now present three times, and sum(order_amount) over that result returns three times the real figure.
| order_id | order_amount | shipment_id | shipment_cost |
|---|---|---|---|
| A-1 | 300 | S-1 | 10 |
| A-1 | 300 | S-2 | 12 |
| A-1 | 300 | S-3 | 9 |
Summing shipment_cost here is right: 31. Summing order_amount gives 900 for an order worth 300. The same result set is at the correct grain for one measure and the wrong grain for the other, which is why the error survives review — the query looks reasonable and the numbers look plausible.
Spotting it
- Count rows before and after the join. If the count grew, something fanned out, and every measure from the multiplied side is now suspect.
- Check the join key’s uniqueness on at least one side. A join is safe for a measure only when the side carrying that measure is not the multiplied one.
- Compare a single entity by hand. Pick one order, query its total directly, and compare with what the joined query reports for it. The ratio tells you the fan-out.
- Watch for totals that grow when a filter is removed or when a new source table is joined in — a sign that the extra table added rows rather than columns.
Four ways to fix it
| Approach | How it works | When it is the right one |
|---|---|---|
| Aggregate before joining | Reduce the many side to one row per key, then join | Generally the clearest; keeps each measure at its own grain |
| Aggregate each measure separately | Compute order totals and shipment totals in separate queries and combine the results at the reporting grain | When measures come from different fact tables — the drill-across case |
| Allocate the value | Split the order amount across its shipments so the parts still sum to the whole | When the report genuinely needs the measure at the finer grain |
| Deduplicate in the aggregate | sum(DISTINCT …) or summing over distinct keys | Only when the repeated values are provably the duplicates — two different orders of exactly 300 would be collapsed into one, which is a new error |
The general defense is to know the grain of every result set before aggregating it, and to treat a join that changes the row count as a change of grain that every subsequent aggregation has to respect.
A different problem with the same name
“Double counting” is also used for a scoring problem: when the same underlying effect is entered under several criteria in an evaluation, its weight is counted more than once. That is a matter of how the criteria were defined rather than of query semantics — it is described in double counting in weighted scoring. The two share a name and nothing else: this one is fixed in the query or the model, that one by redefining the criteria.
How grain decides which measures can be summed together is worked through in Facts, Dimensions, and the Grain That Decides Both.
References: PostgreSQL 18 documentation, Table Expressions; PostgreSQL 18 documentation, Aggregate Functions; Kimball Group, Dimensional Modeling Techniques.
Discover more from Insightful Data Lab
Subscribe to get the latest posts sent to your email.