Total Revenue by Status — GROUP BY in SQL

The problem

Brightlane's finance team is preparing the end-of-quarter revenue review and needs to see how order amounts break down across pipeline stages.

Write a query to return each status and its total order value.

Assumptions:

The orders table contains every order Brightlane has processed.
total_amount is the order's dollar value; the total per status is the sum of total_amount over the rows in that status.

Output:

One row per distinct status, with columns status and total_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
  status,
  SUM(total_amount) AS total_revenue
FROM
  orders
GROUP BY
  status

The shape

SUM(total_amount) paired with GROUP BY status produces a revenue total per pipeline stage instead of one number for the whole table. The aggregate function is the same one you would use without grouping. GROUP BY only changes the unit of aggregation from the entire orders table to each status bucket.

Clause by clause

SELECT status, SUM(total_amount) AS total_revenue returns the status label and the sum of total_amount across every order in that status. The alias gives the column a domain name for the finance review.
FROM orders is the full order history. Every order's total_amount will be added into the bucket matching its status.
GROUP BY status partitions the rows by status before the sum runs. delivered is its own bucket, shipped is its own bucket, and so on. The result is one row per distinct status value.

Why this and not four separate sums

The finance team could write SELECT SUM(total_amount) FROM orders WHERE status = 'delivered' four times and paste the answers together. A single GROUP BY returns all four numbers in one pass and stays correct when a new status is added. The headline number for delivered revenue is 104331.81, and it lands in the same row as its status label, ready for the quarterly review.

You practiced replacing the headline aggregate (SUM over the whole table) with a per-group aggregate. The aggregate function is unchanged — GROUP BY only changes the scope it operates over.

Return each `status` and its total order value

The shape

Clause by clause

Why this and not four separate sums

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