Common Aggregation Mistakes (and How to Avoid Them)

The Hall of Shame

Every senior analyst has a mental list of painful bugs they've shipped. Today, I'm sharing mine so شما don't have to learn the hard way.

Mistake 1: Duplicate Rows in Joins

If شما join an `orders` table to a `products` table, and one product has multiple entries, your `SUM(order_amount)` will be inflated.

-- WRONG: Product has 2 rows -> Order gets counted twice!

SELECT SUM(o.amount) FROM orders o JOIN products p ON o.pid = p.id;

**Fix**: Always check for duplicates in your source tables before joining. Use `COUNT(DISTINCT order_id)` to verify.

Mistake 2: Mixing Aggregated and Non-Aggregated Columns

As we covered on Day 18, this is a syntax error waiting to happen.

-- WRONG in most databases

SELECT country, product_name, SUM(amount) FROM orders GROUP BY country;

**Fix**: Every non-aggregated column must be in the `GROUP BY` list.

Mistake 3: NULL Math

`SUM()` ignores NULLs, but what if *all* values are NULL? The result is NULL, not zero!

-- If no one has a bonus, this returns NULL, not 0

SELECT SUM(bonus) FROM employees;

**Fix**: Use `COALESCE(SUM(bonus), 0)`.

Mistake 4: Integer Division

Calculating a percentage and getting `0` instead of `0.05`?

-- WRONG: Integer math floors to 0

SELECT COUNT(*) / 100 AS rate FROM users;

**Fix**: `SELECT COUNT(*) * 1.0 / 100 AS rate`.

The Victory (Humility)

Every mistake I made taught me something new. The best analysts aren't the ones who never fail—they are the ones who build systems to catch their own errors.

Your Task for Today

Review your most recent `GROUP BY` query. Can شما identify any potential mistakes from this list?

*Day 24: Counting Monthly Active Users (MAU).*