Delivered vs Other Orders per Customer — Conditional Aggregation in SQL

The problem

Brightlane's CRM team wants to know, for each customer, how many of their orders have been delivered and how many have not.

Write a query to return three columns per customer: their ID, their count of delivered orders, and their count of orders in any other status.

Assumptions:

The orders table contains every order Brightlane has processed.
The two counts come from the same per-customer group — both buckets are computed in a single pass.
A customer with all delivered orders contributes 0 to the not-delivered count; a customer with none delivered contributes 0 to the delivered count.

Output:

One row per customer, with columns customer_id, delivered_count, and not_delivered_count.

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(
    CASE
      WHEN status = 'delivered' THEN 1
    END
  ) AS delivered_count,
  COUNT(
    CASE
      WHEN status <> 'delivered' THEN 1
    END
  ) AS not_delivered_count
FROM
  orders
GROUP BY
  customer_id

The shape

Two COUNT(CASE WHEN ... THEN 1 END) expressions split each customer's orders into delivered and not-delivered buckets. The 1 is a placeholder — any non-NULL value would work; the load-bearing part is that unmatched rows fall through to NULL and COUNT skips them. Each count tracks only the rows its predicate admitted, within the per-customer group.

Clause by clause

customer_id is the grouping column and the only non-aggregate column in the SELECT list, which satisfies the N014 rule: every non-aggregate column has to appear in GROUP BY.
COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered_count returns 1 for each delivered row and NULL for everything else. COUNT tallies non-NULL values inside each customer's group. Customer 51 has four delivered orders; customer 22 has zero.
COUNT(CASE WHEN status <> 'delivered' THEN 1 END) AS not_delivered_count covers the complement. The <> operator catches every status that is not 'delivered' — pending, shipped, cancelled, all in the same bucket.
FROM orders GROUP BY customer_id partitions the rows per customer; the two COUNT calls evaluate independently inside each group.

Why `THEN 1` and not `THEN status`

Returning 1 makes the intent obvious: a tally, not a value-of-the-row carrier. THEN status would also work, but the 1 reads as "one mark per matching row," which is what conditional counting is doing.

You practiced COUNT(CASE WHEN ... THEN 1 END) for conditional counting. The convention: returning 1 for matches and letting the unmatched rows fall through to NULL — COUNT skips the NULLs, so the count tracks only the matching rows.

Return three columns per customer: their ID, their count of delivered orders, and their count of orders in any other status

The shape

Clause by clause

Why `THEN 1` and not `THEN status`

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

The shape

Clause by clause

Why THEN 1 and not THEN status

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

Why `THEN 1` and not `THEN status`