Hello Reader,

You've built a clean customer report. Locations, booking history, revenue by region.

You send it off to whoever asked for it.

Ten minutes later: "Why does this report show blank cells in the location column? And why do the totals not add up?"

Welcome to the NULL problem. You're going to learn how to solve it once and for all today.

In the Summit Adventures database (the fake adventure tourism company I created to help people learn business analytics), 78.2% of customers don't have dietary restriction data. 29.1% are missing state information. These aren't errors — they're just fields that weren't required when customers signed up.

But if you don't handle them, your reports will have blank cells (ugh), your aggregations might return unexpected results (yikes), and your executives will question the quality of your work (never a good thing).

The fix is a function called COALESCE.

What COALESCE Does

COALESCE takes a list of values and returns the first one that isn't NULL.

COALESCE(value1, value2, value3, ...)

Think of it as a chain of fallbacks:

• Try the first value. Got something? Use it.
• NULL? Try the second value.
• Still NULL? Try the third.
• All NULL? Return the last value (which should be your default).

The Business Problem: A Clean Location Report

Your marketing team wants a customer report broken down by location. But 29.1% of customers are international. Country but no state. If you just use the state column, nearly a third of your customers show up as blank.

Here's how COALESCE fixes this:

-- Clean location report using COALESCE
SELECT 
    COALESCE(c.state, c.country, 'Unknown') AS location,
    COUNT(*) AS customer_count,
    COUNT(b.booking_id) AS total_bookings,
    ROUND(COALESCE(SUM(p.amount), 0)::numeric, 2) AS total_revenue
FROM customers c
    LEFT JOIN bookings b ON c.customer_id = b.customer_id
        AND b.status IN ('completed', 'confirmed')
    LEFT JOIN payments p ON b.booking_id = p.booking_id
        AND p.payment_status = 'completed'
GROUP BY COALESCE(c.state, c.country, 'Unknown')
ORDER BY total_revenue DESC
LIMIT 10;

No blank cells. No missing data. Every customer accounted for.

Line 3 is doing the work: COALESCE(c.state, c.country, 'Unknown'). For US customers, it uses the state. For international customers (no state), it falls back to the country code. If both are somehow missing, it shows "Unknown."

Notice Line 6 also uses COALESCE: COALESCE(SUM(p.amount), 0). This handles customers who have no payments instead of showing NULL in the revenue column, it shows $0.

Three COALESCE Patterns You'll Use Constantly

Pattern 1: Display defaults for reports

Replace NULLs with readable values:

-- Clean display for customer profiles
SELECT 
    first_name || ' ' || last_name AS customer_name,
    email,
    COALESCE(phone, 'Not provided') AS phone,
    COALESCE(dietary_restrictions, 'None listed') AS dietary_needs,
    COALESCE(state, country) AS location
FROM customers
ORDER BY customer_id
LIMIT 5;

This is the most common pattern. Every NULL becomes something meaningful, and your report looks professional instead of incomplete.

Pattern 2: Safe aggregations

Prevent NULLs from breaking your math:

-- Revenue per customer (including non-buyers)
SELECT 
    c.first_name || ' ' || c.last_name AS customer_name,
    COUNT(b.booking_id) AS bookings,
    COALESCE(SUM(p.amount), 0) AS total_revenue,
    COALESCE(ROUND(AVG(p.amount)::numeric, 2), 0) AS avg_booking_value
FROM customers c
    LEFT JOIN bookings b ON c.customer_id = b.customer_id
        AND b.status IN ('completed', 'confirmed')
    LEFT JOIN payments p ON b.booking_id = p.booking_id
        AND p.payment_status = 'completed'
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_revenue DESC
LIMIT 10;

Without COALESCE, customers with no payments would show NULL instead of $0 and if you try to do further calculations with that NULL, the whole expression becomes NULL.

Pattern 3: Dietary restrictions audit

Understand what proportion of your data is actually populated:

-- Dietary restrictions summary with COALESCE
SELECT 
    COALESCE(dietary_restrictions, 'No restrictions listed') 
        AS dietary_status,
    COUNT(*) AS customer_count,
    ROUND(
        COUNT(*)::numeric / (SELECT COUNT(*) FROM customers) * 100, 1
    ) AS pct_of_total
FROM customers
GROUP BY COALESCE(dietary_restrictions, 'No restrictions listed')
ORDER BY customer_count DESC
LIMIT 10;

Super useful!

Common Mistakes to Avoid with COALESCE

Mistake 1: Using COALESCE when you should be filtering

If NULL rows shouldn't be in your results at all, use WHERE column IS NOT NULL — don't replace them with defaults. COALESCE is for display and calculations, not for hiding data quality problems.

Mistake 2: Defaulting to misleading values

COALESCE(revenue, 0) makes sense. COALESCE(age, 25) is dangerous — you're making up data. Choose defaults that are clearly "no data" rather than plausible values.

Mistake 3: Forgetting COALESCE in GROUP BY

If you use COALESCE(state, country) in your SELECT, you need the same expression in GROUP BY — or you'll get unexpected grouping.

Try This at Your Job

Look at any report you've built recently:

1. Find the blank cells. Which columns have NULLs that confuse the reader?
2. Add COALESCE with a sensible default value ("Not provided", "N/A", or 0 for numeric fields).
3. Check your aggregations. Are any SUM or AVG calculations returning NULL because of missing data? Wrap them in COALESCE.

These are small changes that make your reports look significantly more professional.

Until next time,

Brian

Brian Graves, creator of Analytics in Action Say 👋 on X/Twitter, LinkedIn, or book a call with me. You can always reply to these emails. I check them all.

​

P.S. Handling messy data is one of the core skills in SQL for Business Impact. Module 2 (The Marie Kondo Blueprint) teaches you how to organize, filter, and clean real-world data systematically. Check it out at sqlforbusinessimpact.com.

P.P.S. What's the messiest data you deal with at work? Hit reply and tell me...I might use your scenario (anonymized) in a future newsletter. I read every response.