Categories Above 2000 in Value — Derived Tables in SQL

The problem

Brightlane's buying team is analysing category catalogue value and needs to flag categories with a substantial combined price.

Write a query to return the category ID and total list price for every category whose combined product prices exceed $2,000.

Assumptions:

The products table contains every product in the catalogue.
The combined price is the per-category sum of price.
The threshold (> $2,000) applies to the per-category total.

Output:

One row per qualifying category, with columns category_id and total_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
  category_id,
  total_value
FROM
  (
    SELECT
      category_id,
      SUM(price) AS total_value
    FROM
      products
    GROUP BY
      category_id
  ) AS category_totals
WHERE
  total_value > 2000

The shape

The inner query totals list price per category; the outer query keeps only categories whose total clears $2,000. Same two-pass scaffold as the customer-orders problem — the inner aggregate produces a column the outer WHERE can compare against.

Clause by clause

The inner block sums prices per category:

SELECT category_id, SUM(price) AS total_value
FROM products
GROUP BY category_id

One row per category, with total_value holding the combined list price. - FROM (...) AS category_totals materialises that result as a derived table. The alias is required and also names what the rows represent — readable beats generic when the query grows. - WHERE total_value > 2000 filters the per-category rows. The three categories that survive — 5, 6, and 7 — clear the threshold by a clear margin, with category 6 topping the catalog at 7295. - SELECT category_id, total_value returns exactly the two columns the buying team needs for their catalogue value review. The underlying price column is no longer reachable at this layer; only what the inner SELECT exposed is in scope.

You practiced the same aggregate-then-filter pattern with SUM instead of COUNT. The shape is unchanged: inner query aggregates, outer query filters on the aggregate column.

Return the category ID and total list price for every category whose combined product prices exceed `$2,000`

The shape

Clause by clause

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