Order Status Split per Customer — Conditional Aggregation in SQL

The problem

Brightlane's operations team wants a per-customer order summary covering three figures: total orders placed, delivered count, and not-delivered count.

Write a query to return all three alongside the customer ID, in a single row per customer.

Assumptions:

The orders table contains every order Brightlane has processed.
The total count covers every order in the per-customer group; the breakdown counts each cover only the orders that meet the bucket's condition.
All three figures are computed in a single pass — no extra passes are required.

Output:

One row per customer, with columns customer_id, total_orders, delivered_orders, and not_delivered_orders.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  customer_id,
  COUNT(*) AS total_orders,
  COUNT(
    CASE
      WHEN status = 'delivered' THEN 1
    END
  ) AS delivered_orders,
  COUNT(
    CASE
      WHEN status <> 'delivered' THEN 1
    END
  ) AS not_delivered_orders
FROM
  orders
GROUP BY
  customer_id

The shape

Three aggregates run side by side on the same per-customer group. COUNT(*) sees every order in the group; the two conditional counts see only the rows their CASE admits. The total and the two breakdown buckets line up in one row per customer, with the totals adding to COUNT(*) for every customer because the delivered and <> 'delivered' predicates partition the status values into complementary halves.

Clause by clause

customer_id is the grouping column, present in both SELECT and GROUP BY per the N014 rule.
COUNT(*) AS total_orders counts every row in each per-customer group. The * form is row-count, not value-count — it ignores what's in any column, including the conditional CASE results from the other two aggregates. Customer 1 has 5 orders total; customer 13 has 1.
COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered_orders returns 1 for delivered rows and NULL for everything else. COUNT skips the NULLs, so the tally is the delivered-order count.
COUNT(CASE WHEN status <> 'delivered' THEN 1 END) AS not_delivered_orders covers the complement: pending, shipped, cancelled — anything whose status is not 'delivered'.
FROM orders GROUP BY customer_id partitions the row set into per-customer groups before any aggregate runs.

Why each aggregate sees the right rows

A common misread of conditional aggregation is that the CASE filters rows out of the group before any aggregate runs — like an inline WHERE. It doesn't. The GROUP BY partition still includes every order. The CASE runs once per row, per aggregate, and decides what that row contributes to that specific aggregate. So COUNT(*) and the two conditional counts can sit in the same SELECT and each see a different slice of the same group, in the same pass. That's the property that makes the breakdown-plus-total shape possible without a subquery.

You practiced mixing a plain aggregate (COUNT(*)) with conditional aggregates in the same SELECT. The recurring rule: each aggregate independently decides what it sees from the group — plain aggregates see everything; conditional ones see only the rows their CASE admits.

Return all three alongside the customer ID, in a single row per customer

The shape

Clause by clause

Why each aggregate sees the right rows

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.