Orders with Total Order Count — Scalar Subqueries in SQL

The problem

Brightlane's operations dashboard needs to show each order alongside the total number of orders in the system.

Write a query to return the order ID, total amount, and the overall order count for every order record.

Assumptions:

The orders table contains every order Brightlane has processed.
The total order count is a single number — the same value appears in the third column of every row.

Output:

One row per order, with columns order_id, total_amount, 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 AS order_id,
  total_amount,
  (
    SELECT
      COUNT(*)
    FROM
      orders
  ) AS total_orders
FROM
  orders

The shape

A per-row report needs a global figure next to each row. (SELECT COUNT(*) FROM orders) computes that figure once — 200 — and the outer query attaches it to every order row alongside the row's own id and total_amount. Two different scales of information, one query, one pass.

Clause by clause

SELECT id AS order_id, total_amount reads two columns from the current order row. These change with each row in the output.
(SELECT COUNT(*) FROM orders) AS total_orders is the scalar subquery. PostgreSQL runs the inner SELECT once, gets back the single number 200, and writes that same value into every output row's third column. The subquery doesn't know which order the outer query is currently emitting and doesn't care — it computes a table-wide count that's identical for all 200 outer rows.
FROM orders is the source of the row-level data. Both the outer query and the subquery read from orders, but they're operating at different grains: the outer query at the per-row grain, the subquery at the whole-table grain.

Why this and not two queries

Running the per-row read and the count separately would mean stitching the results together in application code, and a concurrent insert between the two queries could put the count out of sync with the rows. Computing the count inline keeps both numbers tied to the same snapshot of the table, in the same execution, which is what a live dashboard tile needs.

You practiced injecting a global metric (COUNT(*) over the whole table) into every row of a per-row report. The recurring shape any time a dashboard tile needs both the row-level value and a summary statistic next to it.

Return the order ID, total amount, and the overall order count for every order record

The shape

Clause by clause

Why this and not two queries

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