Delivered vs Cancelled Revenue — Conditional Aggregation in SQL

The problem

Brightlane's finance team wants a single-row summary covering two figures:

The combined revenue from delivered orders (status = 'delivered').
The combined revenue from cancelled orders (status = 'cancelled').

Write a query to return both totals in a single row.

Assumptions:

The orders table contains every order Brightlane has processed.
Both totals come from the same scan of the orders table — no WHERE clause restricts rows out, because both buckets are needed in the same row.

Output:

A single row with two columns, delivered_revenue and cancelled_revenue.

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
  SUM(
    CASE
      WHEN status = 'delivered' THEN total_amount
    END
  ) AS delivered_revenue,
  SUM(
    CASE
      WHEN status = 'cancelled' THEN total_amount
    END
  ) AS cancelled_revenue
FROM
  orders

The shape

Two SUM(CASE WHEN ...) expressions route each row's total_amount into the bucket that matches its status. Delivered orders feed delivered_revenue; cancelled orders feed cancelled_revenue; everything else falls out as NULL and is skipped by SUM. One pass, two totals: 104331.81 and 6147.96.

Clause by clause

SUM(CASE WHEN status = 'delivered' THEN total_amount END) AS delivered_revenue evaluates the CASE once per row. When the row's status is 'delivered', the expression returns that row's total_amount; otherwise the CASE falls through with no ELSE and returns NULL. SUM skips the NULLs and totals only the delivered amounts.
SUM(CASE WHEN status = 'cancelled' THEN total_amount END) AS cancelled_revenue does the same on a different predicate. The same row that contributed to the delivered column contributes NULL here, so the two columns tally disjoint slices in the same pass.
FROM orders is the source. There is no WHERE and no GROUP BY: the CASE inside each aggregate does the filtering work that a WHERE would have done on a single-bucket query.

You practiced conditional aggregation: SUM(CASE WHEN ... THEN col END). The recurring shape: each CASE returns the value for matching rows and NULL for the rest; SUM ignores NULLs, so each aggregate sums only its matching slice — multiple buckets in one query, one pass over the data.

Return both totals in a single row

The shape

Clause by clause

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