Aggregate Rows Conditionally With CASE Inside An Aggregate

Use `CASE WHEN` inside `SUM` or `COUNT` to produce multiple metrics from a single pass over grouped data.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Count shipped orders and sum pending amounts per customer

Three separate metrics are computed in one GROUP BY pass. SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) counts only shipped rows. SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) totals amounts only for pending rows. Bob has two shipped orders and zero pending value; Carol has one pending order worth 75.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE orders (
  id INT,
  customer VARCHAR(50),
  status VARCHAR(20),
  amount INT
);

INSERT INTO
  orders (id, customer, status, amount)
VALUES
  (1, 'Alice', 'shipped', 100),
  (2, 'Alice', 'pending', 50),
  (3, 'Bob', 'shipped', 200),
  (4, 'Bob', 'shipped', 150),
  (5, 'Carol', 'pending', 75);

Validated query Shared across supported engines.

SQL

SELECT
  customer,
  COUNT(*) AS total_orders,
  SUM(
    CASE
      WHEN status = 'shipped' THEN 1
      ELSE 0
    END
  ) AS shipped,
  SUM(
    CASE
      WHEN status = 'pending' THEN amount
      ELSE 0
    END
  ) AS pending_value
FROM
  orders
GROUP BY
  customer
ORDER BY
  customer;

Expected result Returned rows for the shared example.

customer	total_orders	shipped	pending_value
Alice	2	1	50
Bob	2	2	0
Carol	1	0	75

Output is identical across all engines.

Useful when

Where this command helps.

counting how many rows per group match different status values in one query
summing amounts separately for each category without multiple subqueries

Explanation

What the command is doing.

Conditional aggregation embeds a CASE WHEN expression inside an aggregate function like SUM or COUNT. Each row contributes a value only when its condition is true — otherwise it contributes zero or null. This lets you pivot status categories, compute split totals, or count matching rows without multiple subqueries.

Aggregate Rows Conditionally With CASE Inside An Aggregate

Count shipped orders and sum pending amounts per customer

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.