Order Summary by Status — CREATE TABLE AS in SQL

The problem

Brightlane's reporting pipeline materializes an order-status summary into a temp table to avoid rerunning the aggregation for every downstream report. The query that populates the temp table needs to return the order count and combined order amount for each status value.

Write a query to return the status, order count, and total amount for each status value.

Assumptions:

The orders table has one row per order with a status and a total_amount.
Each unique status value should appear once in the result.
For each status, the order count is the number of orders carrying that status. The total amount is the combined total_amount across those orders.

Output:

One row per status, with columns status, order_count, and status_total.

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,
  COUNT(*) AS order_count,
  SUM(total_amount) AS status_total
FROM
  orders
GROUP BY
  status

The shape

A GROUP BY status collapses the orders table to one row per distinct status, and the aggregates produce the count and combined amount for each. This is the exact shape a temp table populated by CREATE TEMP TABLE ... AS SELECT would carry: one row per category, with the metrics already rolled up.

Clause by clause

SELECT status, COUNT(*) AS order_count, SUM(total_amount) AS status_total returns three columns. The status value labels each group; COUNT(*) counts the orders inside that group (so delivered reports 161); SUM(total_amount) adds up every order amount in the group.
FROM orders reads every order in the table. Every row participates because no WHERE filter is applied.
GROUP BY status is what makes the aggregates per-status rather than per-table. Without it, COUNT(*) and SUM(total_amount) would collapse the entire table into a single row of two grand totals. With it, the engine sorts rows into one bucket per distinct status value and runs the aggregates inside each bucket separately.

Why this and not the per-row table itself

The pipeline materializes this rolled-up shape, not the raw orders, because the downstream reports want totals by status, not individual order rows. Materializing the aggregated result once means each downstream query reads four rows instead of re-scanning every order and re-running the aggregation. The query you wrote is the canonical body of a CTAS used for exactly that caching purpose.

You practiced producing a per-category aggregation suitable for materialization — the same SELECT shape can either feed a one-shot result set or populate a CREATE TEMP TABLE ... AS SELECT.

Return the status, order count, and total amount for each `status` value

The shape

Clause by clause

Why this and not the per-row table itself

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