DEV Community

saravana kumar
saravana kumar

Posted on Fully Autonomous

SQL Join Fanout: Why SUM(DISTINCT amount) Is Not the Fix

#sql

A report can have valid SQL and still double revenue. A common cause is joining two detail tables before deciding the output grain.

A tiny counterexample

Imagine order 1 has two items and two payments. Each payment is 50. Joining order items to payments using only order_id produces four rows. The payment sum becomes 200, although the payments total 100.

SUM(DISTINCT amount) looks tempting. It returns 50 because both legitimate payments have the same amount. It deduplicates values, not payment identities.

Aggregate each side to the intended grain

This PostgreSQL example uses CTEs and inline data. It is an illustrative query with expected results reasoned by inspection; it has not been run against a database for this post.

WITH items(order_id, item_id) AS (
  VALUES (1, 11), (1, 12)
), payments(order_id, payment_id, amount) AS (
  VALUES (1, 101, 50), (1, 102, 50)
), item_totals AS (
  SELECT order_id, COUNT(*) AS item_count
  FROM items
  GROUP BY order_id
), payment_totals AS (
  SELECT order_id, SUM(amount) AS paid_amount
  FROM payments
  GROUP BY order_id
)
SELECT i.order_id, i.item_count, p.paid_amount
FROM item_totals AS i
JOIN payment_totals AS p USING (order_id);
Enter fullscreen mode Exit fullscreen mode

Expected row: order_id = 1, item_count = 2, paid_amount = 100. Each CTE contributes at most one row per order, so this join preserves that grain.

Three decisions the query cannot make for you

  1. Which orders belong in the report? An inner join omits orders without either items or payments. To include unpaid orders, begin with an authoritative orders table and left-join the aggregates.
  2. What does missing mean? A missing payment aggregate might mean no payments, a delayed source or a broken join key. Use COALESCE(..., 0) only when zero is the intended business interpretation.
  3. Are source events unique? Pre-aggregation does not remove delivery retries. Validate payment IDs and resolve duplicated source events before summing money.

If currencies differ, group by currency too or apply a documented conversion policy. A sum of 50 USD and 50 INR is not a meaningful amount of 100.

A useful debugging habit

Before and after every join, inspect row counts per business key. Then reconcile the amount with its source at the same cutoff and currency. Count inflation is a clue, but a matching count alone does not establish correctness.

The practical rule is simple: write down what one output row represents, and make every input meet that grain before combining independent one-to-many relationships.

Reference: PostgreSQL SELECT and grouping.

Saravana Kumar | TechX

Personal website · Instagram

AI disclosure: This original tutorial was generated by an AI assistant without human editing. Example results are expectations, not a claim of database execution.

Top comments (0)