One table. Three answers. Conditional aggregation in SQL.

Start with the grain

Before choosing a function, write the sentence that describes one output row: one row per merchant. Then define each metric. Paid units are the sum of paid amounts. Refunded units are the sum of refunded amounts. Pending count is the number of pending rows. These are three different questions, even though they share one input table.

Use a small independent example. Amounts below are integer units; they are not floating-point currency.

merchant	status	amount_units
Atlas	paid	120
Atlas	paid	NULL
Atlas	refunded	30
Atlas	pending	80
Birch	pending	NULL
Birch	paid	45

Atlas should produce 120 paid units, 30 refunded units and one pending row. Birch should produce 45, zero and one. Calculate these results before running your query. A plausible-looking table is not enough to prove that the metric is right.

Express the definitions directly

WITH events(merchant, status, amount_units) AS (
  VALUES ('Atlas', 'paid', 120),
         ('Atlas', 'paid', NULL),
         ('Atlas', 'refunded', 30),
         ('Atlas', 'pending', 80),
         ('Birch', 'pending', NULL),
         ('Birch', 'paid', 45)
)
SELECT merchant,
  SUM(CASE WHEN status = 'paid'
      THEN COALESCE(amount_units, 0) ELSE 0 END) AS paid_units,
  SUM(CASE WHEN status = 'refunded'
      THEN COALESCE(amount_units, 0) ELSE 0 END) AS refunded_units,
  SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count
FROM events
GROUP BY merchant
ORDER BY merchant;

CASE decides each row's contribution. SUM combines those contributions within a merchant. COALESCE makes the chosen missing-amount policy explicit: a paid or refunded row with a missing amount contributes zero. That is a business assumption, not a universal rule. Some reports should reject or separately count missing amounts instead.

Verify the output, then challenge it

merchant	paid_units	refunded_units	pending_count
Atlas	120	30	1
Birch	45	0	1

Add a row with an unknown status. Under this definition it contributes to none of the three metrics. If unknown statuses matter operationally, add an unknown count rather than hiding the issue. Add a merchant with only pending events. It should still appear, with zero paid and refunded units.

A merchant with no input rows will not appear at all. To include every registered merchant, begin with a merchant dimension and LEFT JOIN the events. Be careful: COUNT(*) after that join also counts the placeholder row for a merchant without matching events. Count a non-null event ID or use a carefully defined condition instead.

Common mistakes worth catching early

SUM(amount_units) does not count rows. COUNT(amount_units) excludes NULL amounts. A misspelled column cannot be fixed with COALESCE because the name must resolve before the expression runs. Inspect the schema and use the actual column name. Finally, adding a join can multiply rows: confirm the join cardinality before trusting any sum.

The habit to carry forward is simple: define grain, define contributions, calculate a tiny expected result and test edge cases. The SQL follows those decisions.