Total Row Count on Every Row — Window Functions in SQL

The problem

Brightlane's operations report shows every order alongside the platform-wide order count for context.

Write a query to return the ID and status of every order, plus the total number of orders on each row.

Assumptions:

The orders table has one row per order with an id and a status.
The total order count is the number of records in orders. The same value should appear on every output row.

Output:

One row per order, with columns id, status, and total_orders.

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
  id,
  status,
  COUNT(*) OVER () AS total_orders
FROM
  orders

The shape

COUNT(*) OVER () counts every row in the orders table and writes that single number onto every output row, next to each order's own id and status. The empty parentheses after OVER make the window the entire result.

Clause by clause

SELECT id, status returns each order's identifier and status, one row per order.
The window column is:

COUNT(*) OVER () AS total_orders

COUNT(*) counts the rows in its window. OVER () with empty parentheses defines that window as every row in the result, so the count is the total number of records in orders. The same value, 200 for this dataset, appears in total_orders on every row.

FROM orders reads every order. No filter, because the report counts the whole table.

Why this and not a plain `COUNT(*)` with `GROUP BY`

A standalone SELECT COUNT(*) FROM orders returns a single row holding the count. The operations report needs the count attached to every order, not in place of them. The window form keeps every row in the output and adds the aggregate as a side-by-side column, which is exactly the shape the report consumes.

You practiced COUNT(*) OVER () — compute a single record count across the entire result and replicate it onto every row.

Return the ID and status of every order, plus the total number of orders on each row

The shape

Clause by clause

Why this and not a plain `COUNT(*)` with `GROUP BY`

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

The shape

Clause by clause

Why this and not a plain COUNT(*) with GROUP BY

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

Why this and not a plain `COUNT(*)` with `GROUP BY`