Products with Catalog Average Price — Scalar Subqueries in SQL

The problem

Brightlane's pricing team wants to see every product in the catalogue alongside the overall average price across all products.

Write a query to return the product name, its price, and the overall catalogue average price for every product.

Assumptions:

The products table contains every product in the catalogue.
The overall average is a single number — the same value should appear in the third column of every row.
The average is computed over the entire products table, independent of the row being returned.

Output:

One row per product, with columns name, price, and avg_price.

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
  name,
  price,
  (
    SELECT
      AVG(price)
    FROM
      products
  ) AS avg_price
FROM
  products

The shape

The parenthesised SELECT AVG(price) FROM products computes a single number — 326.58... — and slots into the outer SELECT list as if it were a literal. Every product row in the result carries its own name and price alongside that same catalogue average in the third column.

Clause by clause

SELECT name, price reads two columns from the current product row. These vary row by row.
(SELECT AVG(price) FROM products) AS avg_price is the scalar subquery. PostgreSQL runs the inner SELECT once, gets back one row with one column (326.58...), and places that value into every output row. The outer query has no influence on what the inner one computes — both reference products, but the inner aggregate ignores which row the outer query is currently emitting.
FROM products is the source of the per-row data. The outer query walks every product; the subquery's result is reused across that walk.

Why this and not a literal

The average could be precomputed and hardcoded — 326.58 written directly into the SELECT list — but that number stops being accurate the moment a product is added, removed, or repriced. The subquery form recomputes the average every time the query runs, so the third column always reflects the catalogue's current state, not a stale snapshot.

You practiced an uncorrelated scalar subquery in the SELECT list. The recurring shape: a subquery that returns one value computes its result once and reuses that single value across every output row — useful when an aggregate from the same table needs to appear alongside per-row values.

Return the product name, its price, and the overall catalogue average price for every product

The shape

Clause by clause

Why this and not a literal

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