Five Aggregation Traps That Rewrite Your Totals. I Measured Each One on Twelve Rows.

Aggregation functions are where a correct-looking query starts lying. A JOIN trap usually shows up as a row count that looks wrong. An aggregation trap shows up as a single number that looks right. The query runs, the dashboard renders, and the total in the meeting is off by $80 in an $820 month, or the average is $80 when the truth is $68.33.

This is Part 5 of The Practical Data Analyst. Parts 1 to 4 covered window functions, validation checks, JOIN traps, and date traps. This part uses one small table and five experiments. Every number below came out of a script I ran for this article, and the dataset is small enough to check by hand.

The setup: twelve orders, two regions, one NULL

The table is twelve sales rows. Two have no region, and five have no discount:

id	region	rep	units	revenue	discount
1	North	Ana	9	100	10
2	North	Ben	8	80	NULL
3	South	Cid	3	70	5
4	South	Dee	4	70	NULL
5	South	Eli	5	70	5
6	South	Fay	4	70	5
7	South	Gus	3	70	NULL
8	South	Hal	4	70	5
9	South	Ivy	4	70	5
10	South	Jay	3	70	5
11	NULL	Guest	2	50	NULL
12	NULL	Guest2	3	30	NULL

Ground truth, counted by hand: 12 rows, $820 of revenue, 52 units. North holds 2 rows and $180. South holds 8 rows and $560. The two rows with no region hold $80. Keep $820 and 12 in mind. Each trap below breaks one of them in a different way.

Trap 1: Integer division quietly floors your rates

The most common rate in analyst SQL is a sum divided by a count. In SQLite, dividing two integers gives an integer, and the fractional part is dropped without a warning:

SELECT SUM(units) / COUNT(*) FROM sales;      -- 4
SELECT 1.0 * SUM(units) / COUNT(*) FROM sales; -- 4.333333333333333
SELECT SUM(revenue) / COUNT(*) FROM sales;     -- 68
SELECT 1.0 * SUM(revenue) / COUNT(*) FROM sales; -- 68.33333333333333
SELECT SUM(revenue) / SUM(units) FROM sales;  -- 15
SELECT 1.0 * SUM(revenue) / SUM(units) FROM sales; -- 15.76923076923077

Units per order is 4.33, reported as 4. Revenue per order is $68.33, reported as $68. Revenue per unit is $15.77, reported as $15. None of these is a rounding error in the usual sense. The database is not rounding to the nearest whole number, it is truncating toward zero, so 4.99 would also come back as 4. The damage scales with the denominator: on a table of twelve rows the error is a third of a unit, and on a table where the true rate is 1.4, the reported rate is 1, an error of almost 30 percent.

The fix costs four characters. Multiply one side by 1.0, or cast it, before dividing:

SELECT 1.0 * SUM(revenue) / COUNT(*) AS revenue_per_order FROM sales;

Note what is safe here and what is not. AVG() itself always returns a floating point value in SQLite, even on integer input, so AVG(revenue) gives 68.33 correctly. The trap only fires when you write the division yourself, which is exactly what a rate, a share, or a per-unit figure asks you to do.

Trap 2: An average of averages is not the average

Region averages look like a shortcut to the overall average. North averages $90 per order and South averages $70, so the average of those two numbers is $80. The true overall average is $68.33:

SELECT AVG(revenue) FROM sales;  -- 68.33333333333333
 
SELECT AVG(a) FROM (
  SELECT AVG(revenue) AS a FROM sales
  WHERE region IS NOT NULL GROUP BY region
);  -- 80.0

The shortcut fails because the two regions do not carry equal weight. North has 2 orders and South has 8, so the true average is much closer to South's $70 than to North's $90. Averaging the averages gives each region one vote regardless of size. The error here is $11.67 on a $68.33 average, about 17 percent, and it grows as the groups get more unbalanced.

Including the NULL group makes the shortcut wrong in a different direction. The three group averages are $90, $70, and $40, and their average is $66.67, which is also wrong, just on the other side of the truth. Two wrong answers from one bad habit, depending on which rows you include.

This is the same mistake wearing a dashboard costume. A report that averages a daily conversion rate across thirty days is averaging thirty numbers with different denominators, and a quiet Tuesday counts as much as a busy Saturday. The correct computation weights each group by its size, which is what a single AVG() over the raw rows already does. If you need a group-level average, weight it:

SELECT SUM(region_revenue) / SUM(region_orders) AS weighted_avg
FROM (
  SELECT SUM(revenue) AS region_revenue, COUNT(*) AS region_orders
  FROM sales GROUP BY region
);

That returns 68.33, the truth, because each region contributes in proportion to its rows.

Trap 3: COUNT(*) and COUNT(column) count different things

Seven of the twelve rows have a discount. Five do not. Two counts that look interchangeable disagree by five:

SELECT COUNT(*) FROM sales;           -- 12
SELECT COUNT(discount) FROM sales;    -- 7
SELECT SUM(discount) FROM sales;      -- 40.0
SELECT AVG(discount) FROM sales;      -- 5.714285714285714
SELECT AVG(COALESCE(discount, 0)) FROM sales; -- 3.3333333333333335

COUNT(*) counts rows. COUNT(discount) counts rows where the discount is not NULL. The SQLite documentation states this plainly: count(X) returns the number of times X is not NULL, while count(*) returns the total number of rows. The trap is that both are spelled "count", so a reader assumes they agree.

The average discount shows the same split from the other side. AVG(discount) ignores NULLs, so it averages 40 over the 7 rows that have a discount and returns 5.71. Filling the missing values with zero first, with COALESCE(discount, 0), averages the same 40 over all 12 rows and returns 3.33. Both are defensible numbers for different questions: 5.71 is the average discount among discounted orders, 3.33 is the average discount across all orders. The trap fires when a report labels one of them "average discount" without saying which population it means, and a reader assumes the other.

The defence is to name the population in the column alias and to check both counts whenever a column can be NULL. If COUNT(*) and COUNT(column) differ, say so in the report, and say which one the headline number uses.

Trap 4: NULL forms a group of its own

Grouping by region produces three groups, not two. The third one is labelled NULL, and it holds $80:

SELECT region, COUNT(*), SUM(revenue) FROM sales GROUP BY region;

region	COUNT(*)	SUM(revenue)
NULL	2	80
North	2	180
South	8	560

A report that filters to named regions drops the NULL group without mentioning it:

SELECT SUM(revenue) FROM sales WHERE region IS NOT NULL;  -- 740
SELECT SUM(revenue) FROM sales;                            -- 820

The named-region total is $740. The true total is $820. The missing $80 is 9.8 percent of revenue, and it vanishes silently because the filter looks like ordinary hygiene. Nobody writes WHERE region IS NOT NULL intending to lose money. They write it to clean up a chart, and the chart then reports a total that no longer matches the ledger.

The same shape appears whenever a dimension can be missing: a channel, a country, a sales rep. The NULL group is real data, orders that happened and revenue that arrived, attached to a row whose region was never recorded. The fix is a decision, not a trick: either keep the NULL group visible and labelled as unassigned, or exclude it deliberately and show the excluded amount next to the total. What you cannot do is filter it out in the WHERE clause and present the remainder as the whole.

Trap 5: WHERE filters rows, HAVING filters groups

The last trap is the one the database actually catches, and the catch is what teaches the wrong lesson. Ask for regions whose total revenue is over $100, and write the condition in the wrong clause:

SELECT region FROM sales WHERE SUM(revenue) > 100 GROUP BY region;
-- Error: misuse of aggregate: SUM()

SQLite refuses, correctly. The WHERE clause runs before grouping, so there is no sum yet to test. The correct clause is HAVING, which runs after grouping:

SELECT region, SUM(revenue) FROM sales
GROUP BY region HAVING SUM(revenue) > 100;

region	SUM(revenue)
North	180
South	560

That is the right answer: North and South clear $100, the NULL group does not. The trap is what an analyst does after seeing the error. The tempting rewrite is to move the threshold onto the rows instead of the groups:

SELECT SUM(revenue) FROM sales WHERE revenue > 75;  -- 180

That query runs, returns $180, and answers a different question. It sums only the individual orders above $75, which are the two North orders, and silently drops the eight South orders of $70 each. The analyst wanted regions whose total clears $100 and got the sum of large single orders instead. The two numbers happen to coincide here at $180, which makes this instance of the trap easy to miss and the general case dangerous: the coincidence hides the fact that the question changed.

The PostgreSQL tutorial states the rule in one sentence: the WHERE clause determines which rows will be included in the aggregate calculation, so it has to be evaluated before aggregate functions are computed. Read that as a checklist. If the condition mentions a single row, it belongs in WHERE. If it mentions a group total, a group count, or a group average, it belongs in HAVING. If you are unsure which one you have, the presence of SUM, COUNT, or AVG in the condition is the tell.

The safe pattern, in one block

Most of this article compresses into five habits:

-- 1. One floating point operand in every hand-written rate
SELECT 1.0 * SUM(revenue) / COUNT(*) AS revenue_per_order FROM sales;
 
-- 2. Weight group averages by group size, or average the raw rows
SELECT AVG(revenue) FROM sales;
 
-- 3. Check COUNT(*) against COUNT(column) when a column can be NULL,
--    and name the population the headline number covers
 
-- 4. Keep the NULL group visible, or show the excluded amount
--    next to the total when you drop it deliberately
 
-- 5. Row conditions in WHERE, group conditions in HAVING
SELECT region, SUM(revenue) FROM sales
GROUP BY region HAVING SUM(revenue) > 100;

The verdict

None of these five traps, except the misplaced aggregate in Trap 5, produces an error message. Integer division floors a rate, an average of averages ignores group size, COUNT(column) skips NULLs, a region filter drops $80, and a row filter answers a different question from the group filter it replaced. Each wrong number is plausible on its own. The common defence is cheap, and it is the same habit this series keeps returning to: run the query on a table small enough to count by hand, and compare the output with the hand count before the query ships. Twelve rows caught all five traps in this article. A production table would have hidden every one of them.

Key takeaways

1. Dividing two integers in SQLite truncates toward zero: units per order came back as 4 instead of 4.33, revenue per order as $68 instead of $68.33. Multiply one side by 1.0 first.
2. An average of averages gives each group one vote regardless of size: $80 against a true $68.33. Weight by group size, or average the raw rows.
3. COUNT(*) counts rows and COUNT(column) counts non-NULL values: 12 against 7 here. The average discount was 5.71 over discounted orders and 3.33 over all orders, so name the population.
4. NULL forms a group of its own. The NULL region held 2 rows and $80, and a named-region filter dropped 9.8 percent of revenue without saying so.
5. WHERE filters rows before grouping and HAVING filters groups after. A condition with SUM, COUNT, or AVG in it belongs in HAVING.

One question before you go: which of these five is most likely hiding in a report your team already trusts, and what would it cost to check that one report against a hand count this week?

Related on Everyday Data Science:

• Five Date Traps That Move Money Between Months: I Measured Each One
• Five JOIN Traps That Quietly Rewrite Your Numbers. One Orders Table Reported $620, $1,240 and $2,040.
• Most Analytics Bugs Are Data Bugs. Five Checks That Catch Them Before the Meeting.

Sources: SQLite built-in aggregate functions, PostgreSQL tutorial: aggregate functions. All measurements are my own, run for this article in SQLite 3.45.1.