Example 1
Count how many customers are missing an email or phone number
Of the four customers, Bob and Dave have no email (2 nulls), and Carol and Dave have no phone (2 nulls). Each SUM(CASE WHEN ... IS NULL THEN 1 ELSE 0 END) counts the nulls for one column. Adding more columns to the SELECT list extends the audit without additional table scans.
Source table data Rows loaded before the example query runs.
Setup
CREATE TABLE customers (
id INT,
name VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20)
);
INSERT INTO
customers
VALUES
(1, 'Alice', '[email protected]', '555-1234'),
(2, 'Bob', NULL, '555-5678'),
(3, 'Carol', '[email protected]', NULL),
(4, 'Dave', NULL, NULL);Validated query Shared across supported engines.
SQL
SELECT
COUNT(*) AS total_rows,
SUM(
CASE
WHEN email IS NULL THEN 1
ELSE 0
END
) AS missing_email,
SUM(
CASE
WHEN phone IS NULL THEN 1
ELSE 0
END
) AS missing_phone
FROM
customers;Expected result Returned rows for the shared example.
| total_rows | missing_email | missing_phone |
|---|---|---|
| 4 | 2 | 2 |
Identical syntax and result across all engines.