Level 3 · Label the blanks with COALESCE

A NULL means “not recorded”. In a breakdown it either vanishes or shows up as an empty label nobody can explain. COALESCE fixes the label problem.

COALESCE for labels

COALESCE(a, b, c) returns the first argument that is not NULL:

SELECT
  COALESCE(referral_source, 'unknown') AS referral_source,
  COUNT(*) AS customers
FROM customers
GROUP BY 1
ORDER BY customers DESC;

At Harbor & Pine, unknown is the biggest group with 666 customers, ahead of search with 415. Hiding a third of the customer base makes every other share look bigger than it is. The same goes for coupons: of the 4,295 completed orders in 2025, 3,678 have no coupon, and COALESCE(coupon_code, 'none') turns that into a row a reader understands.

You can chain fallbacks, too: COALESCE(nickname, first_name, 'Customer') takes the first value that exists.

How NULLs change aggregates

Aggregates skip NULLs, and the counts look different depending on what you count:

expression	result
COUNT(*)	2,018 (every row)
COUNT(referral_source)	1,352 (non-NULL values only)
COUNT(DISTINCT referral_source)	8
COUNT(DISTINCT COALESCE(referral_source, 'unknown'))	9

Pick the one that matches the question, and use COUNT(*) when you count rows in a group.

Don’t COALESCE numbers to 0

Labels are safe to fill in. Numbers usually are not. Nine products have no unit_cost. The average Furniture cost is 308.11 over the 15 products that have one. With AVG(COALESCE(unit_cost, 0)) it drops to 231.08, because five products now cost $0. A missing cost is unknown, not free. Leave it NULL so AVG skips it, and report how many values were missing next to the average.

Common mistakes

• Counting the column in a NULL group. COUNT(referral_source) returns 0 for the NULL group.
• Mismatched type. COALESCE(unit_cost, 'n/a') fails: every argument must have a compatible type. Turn the number into text first if you really want a label.
• Grouping by the raw column. Group by the same COALESCE expression, or the label and the group disagree.

In SQL Server

COALESCE works the same. T-SQL also has ISNULL(referral_source, 'unknown'), which takes exactly two arguments and returns the type of the first one, so ISNULL on a short NVARCHAR(5) column can cut a longer label off. Prefer COALESCE for portable code.

SELECT ISNULL(referral_source, 'unknown') AS referral_source, COUNT(*) AS customers
FROM customers
GROUP BY ISNULL(referral_source, 'unknown');