How to Debug a SQL Query COUNT and GROUP BY That Double-Counts

Why the totals are doubled

The finance team's daily revenue report is reading roughly double what the raw order data says. Electronics shows about $600, but spot-checking the orders puts it near $300. The numbers are not random - they are inflated by a consistent multiple, which is the fingerprint of a JOIN fan-out.

The report groups order line items by product category and sums the revenue:

SELECT
    p.category,
    COUNT(oi.id) AS total_items,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN product_tags pt ON pt.product_id = p.id
GROUP BY p.category
ORDER BY total_revenue DESC;

The trap is the last JOIN. product_tags holds many rows per product - a Wireless Mouse is tagged both wireless and peripheral, a USB-C Hub has three tags. Because the JOIN matches on product_id alone, every order_items row is duplicated once per tag before SUM and COUNT ever run. A product with two tags counts its revenue twice; a product with three tags counts it three times.

Confirm the row explosion

Before changing anything, prove where the extra rows come from. Count tags per product:

psql -U postgres -d app -c "SELECT product_id, COUNT(*) AS tag_count FROM product_tags GROUP BY product_id ORDER BY product_id;"

Then compare the row count per category with the tags JOIN versus without it. The correct count comes straight from order_items:

psql -U postgres -d app -c "SELECT p.category, COUNT(*) FROM order_items oi JOIN products p ON p.id=oi.product_id GROUP BY p.category;"

Add the JOIN product_tags pt ON pt.product_id = p.id and the count balloons - that gap is exactly the duplication the SUM is charging you for.

The fix - drop the join the report never needed

The report groups by category and sums quantity * unit_price. Nothing in the SELECT, GROUP BY, or SUM references a tag. The product_tags JOIN adds no column and no value - it only multiplies rows. Remove it:

SELECT
    p.category,
    COUNT(oi.id) AS total_items,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.category
ORDER BY total_revenue DESC;

Rerun the script and the totals match the real orders:

python3 report.py

The grand total lands at $609.88 instead of the inflated figure, and each category now reflects the actual line-item revenue.

When you actually need the tagged table

Sometimes you genuinely need a one-to-many table in the query - say, to filter by tag. Do not let it fan out your aggregate. Two safe patterns:

• Aggregate first, join later. Compute the per-category total in a subquery, then join the tag data to that result so the SUM is already final.
• Pre-collapse the many side. Use EXISTS for a filter, or SELECT DISTINCT / a grouped subquery so each product contributes exactly one row before the SUM.

The general rule: a JOIN to a table with multiple rows per key multiplies your grain. Always check the row count at each stage of a query - if a JOIN doubles the rows, it will double every SUM and COUNT that follows.