MySQL Aggregate Mistakes: Double Counting Joins

A query that runs is not a query that is right. These are the classic ways to get a wrong total without any error.

query.sql

SQL

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    ordered_on DATE NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL
);

INSERT INTO customers (name, city) VALUES
    ('Maya Shah',    'Pune'),
    ('Rohan Verma',  'Delhi'),
    ('Sana Ali',     'Pune'),
    ('Tara Nair',    'Kochi'),
    ('Uday Singh',   'Delhi');

INSERT INTO orders (customer_id, ordered_on, total, status) VALUES
    (1, '2026-05-02',  680.00, 'delivered'),
    (1, '2026-05-20', 1299.00, 'delivered'),
    (2, '2026-05-21',  145.00, 'shipped'),
    (3, '2026-06-01',  450.00, 'delivered'),
    (3, '2026-06-05',   80.00, 'cancelled'),
    (3, '2026-06-18',  999.00, 'shipped'),
    (2, '2026-06-20',  220.00, 'pending');

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product VARCHAR(20) NOT NULL,
    quantity INT NOT NULL
);
INSERT INTO order_items (order_id, product, quantity) VALUES
    (1, 'Notebook', 2), (1, 'Pen', 5), (1, 'Marker', 1),
    (2, 'Backpack', 1),
    (3, 'Marker', 1);

1. Join fan-out: double counting

The total of orders is:

query.sql

SQL

SELECT SUM(total) AS real_revenue, COUNT(*) AS real_orders FROM orders WHERE id IN (1, 2, 3);

Output

OUTPUT

+--------------+-------------+
| real_revenue | real_orders |
+--------------+-------------+
|      2124.00 |           3 |
+--------------+-------------+
1 row in set

Now join the items, one row per order item, and sum again:

query.sql

SQL

1
2
3

SELECT SUM(o.total) AS inflated_revenue, COUNT(*) AS rows_counted
FROM orders o
JOIN order_items i ON i.order_id = o.id;

Output

OUTPUT

+------------------+--------------+
| inflated_revenue | rows_counted |
+------------------+--------------+
|          3484.00 |            5 |
+------------------+--------------+
1 row in set

Order 1 has three items, so its total of 680 is added three times. The join multiplied the order rows. Rule: aggregating a column from the “one” side after joining to the “many” side counts it once per child.

Fixes: aggregate before joining, or aggregate in separate subqueries.

query.sql

SQL

SELECT o.id, o.total, s.item_count, s.units
FROM orders o
JOIN (SELECT order_id, COUNT(*) AS item_count, SUM(quantity) AS units FROM order_items GROUP BY order_id) s
  ON s.order_id = o.id
ORDER BY o.id;

Output

OUTPUT

+----+---------+------------+-------+
| id | total   | item_count | units |
+----+---------+------------+-------+
|  1 |  680.00 |          3 |     8 |
|  2 | 1299.00 |          1 |     1 |
|  3 |  145.00 |          1 |     1 |
+----+---------+------------+-------+
3 rows in set

Each order appears once. For the grand total, use SUM(total) from orders alone. When you must count parents after a join, use COUNT(DISTINCT o.id).

2. Average of averages

Covered in SUM and AVG. Averaging group averages gives each group the same weight, regardless of its size. Calculate from the rows.

3. NULL and the average

AVG(column) skips NULL. If NULL means zero, the average is too high:

query.sql

SQL

SELECT AVG(points) AS skips_null, AVG(COALESCE(points, 0)) AS counts_null_as_zero FROM (SELECT 10 AS points UNION ALL SELECT NULL UNION ALL SELECT 20) AS t;

Output

OUTPUT

+------------+---------------------+
| skips_null | counts_null_as_zero |
+------------+---------------------+
|    15.0000 |             10.0000 |
+------------+---------------------+
1 row in set

4. COUNT(*) after a LEFT JOIN

With a LEFT JOIN, a parent without children still gives one row, so COUNT(*) reports 1 for “no children”:

query.sql

SQL

SELECT c.name, COUNT(*) AS wrong_count, COUNT(o.id) AS right_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

Output

OUTPUT

+-------------+-------------+-------------+
| name        | wrong_count | right_count |
+-------------+-------------+-------------+
| Maya Shah   |           2 |           2 |
| Rohan Verma |           2 |           2 |
| Sana Ali    |           3 |           3 |
| Tara Nair   |           1 |           0 |
| Uday Singh  |           1 |           0 |
+-------------+-------------+-------------+
5 rows in set

Tara and Uday have no orders. COUNT(*) says 1, COUNT(o.id) says 0. Count a column from the optional side.

5. Grouping on the wrong level

If you group by a name and two different people have the same name, they merge:

query.sql

SQL

SELECT name, COUNT(*) AS people FROM (SELECT 'Asha' AS name, 1 AS id UNION ALL SELECT 'Asha', 2 UNION ALL SELECT 'Ben', 3) AS t GROUP BY name ORDER BY name;

Output

OUTPUT

+------+--------+
| name | people |
+------+--------+
| Asha |      2 |
| Ben  |      1 |
+------+--------+
2 rows in set

Group by the key (id), and display the name as an extra grouped column.

6. WHERE vs HAVING

A condition on a plain column belongs in WHERE. A condition on an aggregate belongs in HAVING. Filtering rows first (WHERE) is faster and avoids mistakes (HAVING).

7. SUM of text or dates

SUM('abc') gives 0 with a warning. SUM of dates gives a meaningless number. Check types first.

8. Integer overflow and precision

SUM of INT values returns a DECIMAL, so it does not overflow. A sum of FLOAT values may lose precision on large totals. Use DECIMAL for money.

A checklist for any aggregate query

What is one row in the result? (one customer, one day, one product)
What is one row in the source? If the source has more rows per result row (a join), expect fan-out.
Does a NULL change the meaning?
Do the parts add up? Compare a total with a simpler query.
Test with a tiny table where you can calculate the answer by hand.

lightbulb

Verify with a second query

After a complicated report query, run a simple one (SELECT SUM(total) FROM orders) and compare. If they disagree, the join has multiplied rows.