Data Analysis & SQL · Practitioner lesson 8 of 19

SQL COALESCE Example: Sum Totals With NULL Values

Learn how SELECT, FROM, WHERE, and COALESCE prevent blank values from ruining database calculations.

Why this matters

A blank value can silently break your totals, and nobody notices. Handling blanks correctly keeps money reports accurate.

Blank Values Break Math

A blank value is called NULL. Any math with NULL gives NULL, so those rows quietly drop out of your totals.

In real life: In a form, a blank box does not mean zero. It means "not filled in". SQL treats it the same way, and any sum that touches it becomes "unknown".

COALESCE Turns Blank Into Zero

COALESCE(discount, 0) means: use the discount if there is one, otherwise use 0. Now the math always works.

In real life: A cashier sees a blank discount field and treats it as "no discount". COALESCE tells SQL to do the same: if blank, use 0.

Remove Unwanted Rows With WHERE

WHERE status = 'COMPLETED' removes cancelled and refunded orders before SUM adds up the money.

In real life: Before adding up the day's sales, you remove the cancelled orders from the pile. WHERE does that clean-up first, so the total is honest.

Your practice task

Calculate total net revenue (amount minus discount) across completed orders. Some orders have missing discount values — ensure missing discounts count as 0 rather than dropping rows, and name the output net_revenue.

How to do it

Open THE DATA panel and find the blank discounts.
Work out what should happen to a blank discount in the math.
Add up the net amount for completed orders only.
Press Run proof and read the checklist.

Practice it for free in the app. You get instant checks that show what you got right and what to fix. A lesson takes about two minutes and you can try as many times as you like.

Quick reference: Blank-Safe Total Query

Stops blank cells from silently dropping rows out of a total.

Practice this lesson free

More lessons in this course

See all 19 lessons in Data Analysis & SQL