Counting NULLs per column with SUM(CASE WHEN ... IS NULL)

Counting NULLs per column works by scoring each row - one point for missing, zero for present - then adding those scores up into a single total per column.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Counting Missing Values Across Retail Order Columns

SELECT
  SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS null_customer_id,
  SUM(CASE WHEN ship_date IS NULL THEN 1 ELSE 0 END) AS null_ship_date,
  SUM(CASE WHEN discount IS NULL THEN 1 ELSE 0 END) AS null_discount,
  SUM(CASE WHEN region IS NULL THEN 1 ELSE 0 END) AS null_region
FROM orders

Result:

null_customer_id	null_ship_date	null_discount	null_region
1	2	2	1

Step by step

One WHEN/THEN pair handles a single condition; rows that don't match get NULL (or the ELSE if present).
```
SELECT
  CASE
    WHEN customer_id IS NULL THEN 1
    ELSE 0
  END AS category
FROM orders;;
```

ELSE catches every row that didn't match any WHEN - gives you a default label.

SELECT
  CASE
    WHEN customer_id IS NULL THEN 1
    ELSE 0
  END AS category
FROM orders;;

Practice

Try each one in your head first, then open the answer.

1. Using customers, write a query to count how many NULLs are in the email column.

SELECT SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) FROM customers

2. Using products, write a query to count how many NULLs are in the product_id column.

SELECT SUM(CASE WHEN product_id IS NULL THEN 1 ELSE 0 END) FROM products

3. Using orders, write a query to count how many NULLs are in the customer_id column.

SELECT SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) FROM orders

4. Using inventory, write a query to count how many NULLs are in the stock_count column.

SELECT SUM(CASE WHEN stock_count IS NULL THEN 1 ELSE 0 END) FROM inventory

5. Using reviews, write a query to count how many NULLs are in the review_date column.

SELECT SUM(CASE WHEN review_date IS NULL THEN 1 ELSE 0 END) FROM reviews

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed