Order Count Revenue and Average Value — Aggregate Functions in SQL

The problem

Brightlane's finance team is compiling the monthly performance summary and needs three headline figures from the orders history:

The total number of orders
The total revenue across all orders
The average order value

Write a query to return all three in a single row.

Assumptions:

The orders table contains every order Brightlane has processed.
total_amount is the order's total dollar value.

Output:

A single row with three columns: order_count, total_revenue, and avg_order_value.

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
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_revenue,
  AVG(total_amount) AS avg_order_value
FROM
  orders

The shape

Three aggregates over the same orders table — one row count, one revenue sum, one average order value — composed into a single summary row. The result is the operations dashboard's three headline numbers in one query: 200 orders, 126725.73 in revenue, 633.62865 average.

Clause by clause

COUNT(*) AS order_count counts every row in orders. The * form counts rows regardless of whether any column is NULL, which is what the dashboard wants for "how many orders did we process."
SUM(total_amount) AS total_revenue adds up the total_amount column across every row. Rows where total_amount is NULL would be skipped, which is correct here — an order with no recorded total has no contribution to revenue.
AVG(total_amount) AS avg_order_value returns the average order size. PostgreSQL computes this as the sum of non-NULL total_amount values divided by the count of non-NULL values, not divided by the row count. Rows where total_amount is NULL are excluded from both the numerator and the denominator.
All three aggregates read the same orders table in the same pass. PostgreSQL walks the rows once and updates each aggregate as it goes. The comma-separated SELECT list is how multiple summary numbers come back in a single row.

Why this and not three separate queries

Each aggregate could live in its own query, but running them together saves work and keeps the dashboard's three numbers in lock-step with one input pass. If two queries run against orders at different times — even a few milliseconds apart — a concurrent order can land between them, and the count, sum, and average no longer describe the same set of rows. The single-query form guarantees the three numbers are computed over identical inputs.

You practiced stacking three aggregates in one SELECT. Every aggregate operates independently over the same row set — combining them in one query is the recurring shape behind any headline-numbers dashboard tile.

Return all three in a single row

The shape

Clause by clause

Why this and not three separate queries

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