SQL Conditional Aggregation: SUM CASE and COUNT CASE

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

CASE chooses a value for each input row, and the surrounding aggregate combines those chosen values. SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) counts paid records by adding one for a match and zero for a nonmatch. The same shape can add amounts instead of ones. COUNT(CASE WHEN status='paid' THEN 1 END) also counts paid rows because the omitted ELSE yields NULL for nonmatches and COUNT(expression) ignores NULL.

Do not add ELSE 0 inside that COUNT expression when you intend to count only matches. Zero is non-NULL, so COUNT(CASE ... ELSE 0 END) counts every input row. SUM and COUNT treat zero differently because their jobs differ: SUM adds its numeric contribution, whereas COUNT counts its presence. Read both the CASE and aggregate together before assuming two expressions are equivalent.

Place a condition in WHERE when it should restrict every measure in the report. Place it inside CASE when it should restrict only one measure while other rows remain available for other columns. WHERE status='paid' followed by a pending-count CASE always produces zero pending matches, because the pending rows were removed before aggregation. A shared date range belongs in WHERE when all status measures refer to the same period.

Categories may overlap if their conditions overlap. A high-value count and a paid count can both count the same sale, which is valid when they are separate measures but misleading if presented as mutually exclusive buckets. Use a single ordered CASE when each row must enter exactly one category. Missing amounts deserve a separate bucket if completeness matters. A condition amount >= 20 is unknown on sale 104, so its ELSE branch receives the row unless you write a dedicated IS NULL branch first.

CASE inside COUNT or SUM is supported by all four systems. PostgreSQL and SQLite also support aggregate FILTER syntax, but MySQL and SQL Server do not share that syntax. CASE is the portable form for these conditional measures. Boolean-expression arithmetic is another dialect-sensitive shortcut to avoid in a shared reference.

Check the engine-specific syntax and version requirements in PostgreSQL aggregate expressions.

Count known amounts and missing amounts as two separate columns using conditional aggregation.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT
 SUM(CASE WHEN amount IS NOT NULL THEN 1 ELSE 0 END) AS known_count,
 SUM(CASE WHEN amount IS NULL THEN 1 ELSE 0 END) AS missing_count
FROM sales;

known_count | missing_count
5 | 1

The two predicates are complementary. Every sale contributes exactly one to one column, so five plus one equals the six-row population. A known zero would contribute to known_count, not missing_count, which preserves the difference between absence and a real numeric value.

Replace the paid-count SUM with COUNT(CASE WHEN status='paid' THEN 1 ELSE 0 END) and predict six. Remove ELSE 0 and predict four. Then add a shared paid-status WHERE to the multi-status summary and explain why pending_count becomes zero: its records have been removed before every CASE expression gets a chance to evaluate them.