Why Does My SQL Query Return the Correct Rows but the Wrong Numbers?

Live example: watch the SUM double

Below is a real, three-table schema — customers, orders, and order_items — running in an actual SQLite engine in your browser, not a screenshot. Run the buggy query first, then the correct one, and watch the customer count stay at 5 rows both times while the revenue changes underneath it.

Notice what didn't change: both queries return exactly 5 rows — one per customer — because both group by c.name. What changed is the value inside those rows. That's the entire trap: a reviewer skimming row counts, or a dashboard showing "5 customers reporting revenue," would see nothing wrong at all.

Why this happens: row-count checks and value checks are blind to each other

A GROUP BY query's row count is determined by how many distinct groups exist in whatever the query is grouping on — nothing else. The value inside each group is determined by every row that was fed into the aggregate function for that group, including rows that were duplicated by an earlier join. These are two completely independent pieces of query logic, which is exactly why a bug in one is invisible from the other:

SELECT c.name AS customer_name, SUM(o.total_amount) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id  -- fans out one order row per item
GROUP BY c.name;                        -- still exactly 5 groups

The fix is to never let an aggregate on the one-side table (orders.total_amount) see a row that's been duplicated by a many-side join (order_items). Pre-aggregate the many side into its own subquery or CTE first, so it collapses back down to one row per order before it ever touches the join:

WITH item_counts AS (
  SELECT order_id, COUNT(*) AS item_count
  FROM order_items
  GROUP BY order_id
)
SELECT
  c.name AS customer_name,
  SUM(o.total_amount) AS revenue,     -- correct: one order row per join match
  SUM(ic.item_count) AS item_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN item_counts ic ON ic.order_id = o.id
GROUP BY c.name;

This still reports item counts alongside revenue — it just never lets order_items multiply an orders row before the aggregate runs. For the full mechanics of this specific pattern, see Why Is My SQL SUM() Wrong After a JOIN?, which walks through three different fixes including why SUM(DISTINCT ...) is a tempting but fragile shortcut.

Cause 2: a many-to-many join silently duplicating rows

Fan-out isn't limited to one-to-many joins. Joining two tables that both have multiple matching rows on the join key — a classic many-to-many join, like students to courses through an enrollment table — multiplies rows on both sides. If the resulting row count happens to match what you expected (easy to happen with small test data), the inflated aggregate underneath it can go unnoticed for a long time.

-- Each product can have several tags; each tag applies to several products.
-- Summing price here counts every product once per tag it has.
SELECT category, SUM(p.price) AS total_price
FROM products p
JOIN product_tags pt ON pt.product_id = p.id
JOIN tags t ON t.id = pt.tag_id
GROUP BY category;

The fix is the same principle as before: aggregate one side down to a unique key before joining, or aggregate on DISTINCT p.id semantics rather than summing a value that a fan-out join has duplicated.

Cause 3: the wrong JOIN type dropping rows that happen to balance out

An INNER JOIN where a LEFT JOIN was needed silently drops parent rows that have no match on the child side — for example, customers with zero orders disappearing entirely from a "revenue by customer" report instead of showing $0. If the number of customers with no orders happens to equal the number of some other filtered-out edge case, the total row count can coincidentally match expectations while specific rows and their values are simply missing. See INNER JOIN vs LEFT JOIN vs RIGHT JOIN for the full comparison.

Cause 4: integer division truncating a ratio

In dialects where dividing two integer columns performs integer division, a ratio like completed_orders / total_orders truncates to 0 whenever the numerator is smaller than the denominator — every single row still comes back, just with a silently wrong value.

-- Wrong on engines with integer division: truncates toward 0
SELECT region, completed_orders / total_orders AS completion_rate
FROM region_stats;

-- Fixed: force floating-point division
SELECT region, completed_orders * 1.0 / total_orders AS completion_rate
FROM region_stats;

A 4-step checklist for wrong numbers with right row counts

1. Compare COUNT(*) before and after every JOIN — added in the query, not just at the end — to catch fan-out the moment it's introduced.
2. Re-run the aggregate on the base table alone, with no joins at all, and compare it to the joined version. Any gap means a join is duplicating rows the aggregate function sees.
3. Check whether the join type matches the relationship you actually need — LEFT JOIN when parent rows without a match should still appear with zero or NULL values.
4. Check the data types feeding any division — cast at least one operand to a float/decimal type before dividing two integer columns.

Challenge: fix it yourself

Same dataset, three tasks, increasing difficulty. Load the starter, write your query in the editor above, then check your answer — everything runs and validates instantly in your browser.