In one SQL query, how do you compute each customer's cancellation rate with conditional aggregation?

## The shape of a rate A per-group rate is a conditional aggregate over a total, both computed in the same `GROUP BY`: ```sql SELECT customer_id, COUNT(*) AS orders, SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled, 100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*) AS cancel_pct FROM orders GROUP BY customer_id; ``` Every row still reaches the grouping, so the denominator is the customer's full order count and the numerator measures the subset. Exposing the raw numerator and denominator beside the percentage is a habit worth keeping: a rate of 100% reads very differently when the denominator is 1 than when it is 4,000. ## Trap 1: integer division In several engines — PostgreSQL and SQL Server among them — dividing an integer by an integer performs integer division and truncates toward zero, so `3 / 10` is `0` and the whole report reads 0%. Others promote the operands to a decimal type. Because the behavior differs, write the query so it does not depend on the engine: multiply by a decimal literal (`100.0 *`), or cast explicitly with `CAST(SUM(CASE …) AS decimal(10,2))`. Placing the `100.0` at the front of the expression also fixes the ordering problem — multiplying first keeps precision that dividing first would have thrown away. ## Trap 2: a zero denominator `COUNT(*)` cannot be zero inside a group, because a group exists only if it has at least one row. That safety disappears the moment the denominator is itself conditional: ```sql -- refund rate among *paid* orders only SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END), 0) ``` A customer with no paid orders would divide by zero, and in most engines that raises an error that kills the entire query rather than one row. `NULLIF(x, 0)` returns NULL when `x` is 0, and dividing by NULL yields NULL — so the offending group reports "undefined" and the rest of the report survives. If a literal 0 is wanted instead, wrap the whole expression in `COALESCE(…, 0)`, but consider whether "no denominator" really means "zero percent" or "not applicable". ## The AVG shortcut Because a rate is the mean of a 0/1 indicator, `AVG` expresses it directly: ```sql AVG(CASE WHEN status = 'cancelled' THEN 1.0 ELSE 0.0 END) AS cancel_rate ``` This returns a fraction between 0 and 1 with no explicit division and no zero-denominator risk. Write the branch values as `1.0`/`0.0` rather than `1`/`0`, so the averaging happens in numeric arithmetic regardless of how the engine types integer aggregates. Note the difference from `AVG(CASE WHEN status = 'cancelled' THEN 1.0 END)`: with no `ELSE`, non-matching rows are NULL, aggregates skip NULL input, and the average of a column of 1.0s is always 1.0 — a constant, not a rate. The `ELSE` is what makes this form work. ## Weighted ratios The same structure answers value-weighted questions by putting a measure in the `THEN` branch: ```sql SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) / NULLIF(SUM(amount), 0) AS cancelled_value_share ``` Here the denominator is an unconditional `SUM`, which *can* legitimately be 0 (or NULL, if every amount is NULL), so the `NULLIF` guard is not optional. ## Readability: aliases do not travel sideways SQL does not let one `SELECT`-list expression reference an alias defined next to it, so the ratio cannot be written as `cancelled / orders` in the same list. The two options are to repeat both expressions inside the division, or to compute the components once and divide one level up: ```sql WITH per_customer AS ( SELECT customer_id, COUNT(*) AS orders, SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled FROM orders GROUP BY customer_id ) SELECT customer_id, orders, cancelled, 100.0 * cancelled / orders AS cancel_pct FROM per_customer; ``` For two measures, repetition is fine; past that the second form is the one people can actually review. ## Rounding and presentation Round at the edge, not in the middle: `ROUND(100.0 * … / COUNT(*), 2)`. Rounding intermediate values before further arithmetic accumulates error, and rounding a rate to an integer percentage hides the difference between 0.4% and 0%. Whether rates belong in SQL at all is a judgment call — the query is the right place when the consumer needs a sorted or filtered ranking of rates, and the wrong place when a dashboard is going to reformat the number anyway.