Order Count by Status — GROUP BY in SQL

The problem

Brightlane's fulfilment director is reviewing pipeline health ahead of the monthly board report.

Write a query to return the number of orders in each status.

Assumptions:

The orders table contains every order Brightlane has processed.
The status column has a small handful of values; the result will have one row per status.

Output:

One row per status value, with columns status and order_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
  status,
  COUNT(*) AS order_count
FROM
  orders
GROUP BY
  status

The shape

GROUP BY status partitions the orders table into one bucket per distinct status value, and COUNT(*) then runs once inside each bucket. The output has one row per status because that is the unit the count is now scoped to.

Clause by clause

SELECT status, COUNT(*) AS order_count returns the status label for each group alongside its row count. status is fine in the SELECT list because it appears in GROUP BY; COUNT(*) is fine because it is an aggregate.
FROM orders reads the full orders table as the input population.
GROUP BY status is what changes COUNT(*) from a single grand total into a per-status total. Without this clause the query would return one row with the total count across every status combined.

Why this and not a separate query per status

A single GROUP BY does the job of four hand-written WHERE status = '...' counts. The fulfilment director sees delivered at 161, shipped at 17, pending at 11, cancelled at 11 from one pass over the table, and any new status value that gets added later shows up automatically. Writing four separate counts hard-codes the status list into the query and breaks silently the first time a fifth status appears.

You practiced partitioning a table with GROUP BY so an aggregate runs once per partition. The recurring shape: any time a question is "X by Y" (orders by status, revenue by region), the Y goes in GROUP BY and the aggregate runs per group.

Return the number of orders in each `status`

The shape

Clause by clause

Why this and not a separate query per status

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