SQL aggregation without misleading counts

Start with the grain

“Revenue by customer” needs one output row per customer. Orders are at a different grain: one row per order. Joining a customer to three orders creates three joined rows. That is useful for summing order amounts, but counting joined rows no longer counts customers. State your intended grain before choosing aggregate functions.

Use stable identifiers in GROUP BY. Two customers can share the name Ada; grouping only by name would combine them. In an interview, clarify whether the output needs every customer, only customers with paid orders, or only customers above a revenue threshold. These are three different questions.

Run a complete example

This self-contained query creates three customers and four orders. It reports all customers, counts paid orders and sums the amounts that are known. A pending order should not count; a paid order with an unknown amount should count as an order, but cannot establish revenue. Copy the whole query into a PostgreSQL query editor, or the public SQL practice editor, to reproduce the output.

WITH customers(customer_id, name) AS (
  VALUES (1, 'Ada'), (2, 'Bo'), (3, 'Cy')
), orders(order_id, customer_id, status, amount_cents) AS (
  VALUES (10, 1, 'paid', 1200), (11, 1, 'paid', 800),
         (12, 2, 'pending', 500), (13, 2, 'paid', NULL::integer)
)
SELECT c.customer_id, c.name,
       COUNT(o.order_id) AS paid_orders,
       COALESCE(SUM(o.amount_cents), 0) AS known_total_cents
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id AND o.status = 'paid'
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

Expected output:

Read each output row

Ada has two paid orders worth 1,200 and 800 cents, so the count is 2 and the sum is 2,000. Bo has one pending order, which the join excludes, and one paid order whose amount is NULL. COUNT(order_id) returns 1 for Bo. SUM ignores the NULL amount and returns NULL when it has no non-NULL inputs; COALESCE changes that result to 0. Cy has no orders, so the LEFT JOIN contributes an unmatched row with NULL order columns. COUNT(order_id) returns 0.

The label known_total_cents matters. Bo’s 0 means no known amount was summed, not proof that the actual amount is zero. If missing amounts should invalidate the report, include a separate missing-value count or leave the sum NULL instead. A business rule should choose the treatment of missing data.

COUNT(*) and COUNT(column) answer different questions

COUNT(*) counts joined rows, including the unmatched row produced for Cy. Replacing COUNT(o.order_id) with COUNT(*) would incorrectly report one paid order for Cy. COUNT(column) counts only non-NULL values. Choose a column that is non-NULL on genuine orders, normally the order primary key.

COUNT(DISTINCT customer_id) can count unique customers, but it does not fix every join problem. If each order is joined to multiple line items, summing the order total duplicates money. Aggregate the line-item table to one row per order first, or calculate the sum from the table with the correct grain. Do not use SUM(DISTINCT amount) as a repair: two different orders may legitimately have the same amount.

Place filters deliberately

WHERE filters rows before grouping. HAVING filters groups after their aggregate values have been calculated. To return only customers whose known paid total reaches 1,000 cents, add HAVING COALESCE(SUM(o.amount_cents), 0) >= 1000 before ORDER BY. The example would then return Ada only.

The paid-order condition belongs in this LEFT JOIN’s ON clause because the question includes customers with no paid orders. Putting o.status = 'paid' in WHERE removes the NULL unmatched rows, changing which customers survive. An INNER JOIN is appropriate when the question intentionally excludes customers without a matching paid order.

Check edge cases before you finish

Test no orders, only pending orders, NULL amounts, repeated names and multiple orders with the same amount. Ask whether refunds are negative amounts, separate records or excluded statuses. Store money in integer cents or an appropriate exact numeric type; avoid introducing floating-point rounding into financial totals.

Explain the grain, join choice, filter placement and NULL rule in that order. Then compare actual output to expected rows. For hands-on work, browse SQL problems; for the missing-match reasoning behind this join, read NULL, joins and missing customers. These examples are learning material, not a secure assessment or employment credential.