Brightlane's luxury product team is auditing the catalog by category.
Write a query to return every category ID, the total product count, the count of products priced above $1,000, and the combined price of those luxury products.
Assumptions:
- Each
category_idwith at least one product should appear once. - For each category, the total count covers every product. The luxury count covers only products with
pricestrictly greater than$1,000. The luxury total is the combinedpriceacross those luxury products. - Categories with zero luxury products show a luxury count of
0and a missing luxury total.
Output:
- One row per category, with columns
category_id,total_products,luxury_count, andluxury_total.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
category_id,
COUNT(*) AS total_products,
COUNT(*) FILTER (
WHERE
price > 1000
) AS luxury_count,
SUM(price) FILTER (
WHERE
price > 1000
) AS luxury_total
FROM
products
GROUP BY
category_idThe shape
The same condition — price > 1000 — restricts a COUNT and a SUM to the luxury subset of each category_id partition. The two aggregates behave differently when no row in the partition matches: COUNT returns 0 because it counts the empty set as zero, and SUM returns NULL because there is nothing to add. That divergence is the audit's tell — every category with zero luxury products shows a luxury count of 0 and a NULL luxury total.
Clause by clause
SELECT category_id, COUNT(*) AS total_products, COUNT(*) FILTER (WHERE price > 1000) AS luxury_count, SUM(price) FILTER (WHERE price > 1000) AS luxury_totalreturns the category, the full product count, the luxury-only count, and the luxury-only price total. The unfilteredCOUNT(*)runs across every row; the two filtered aggregates run only across rows wherepriceexceeds$1,000.FROM productsreads the product records.GROUP BY category_idpartitions the rows per category, including the partition wherecategory_idis NULL — every NULL collapses into one group, so the three uncategorised products land in one row.
Why SUM over zero rows returns NULL and not 0
SUM is defined to add the values it sees. When it sees zero values, there is nothing to add, and the result is NULL. COUNT is defined differently: it counts the rows it sees, and counting zero rows produces 0. The two functions diverge on empty inputs by design. The same divergence holds for AVG, MIN, and MAX — all return NULL on an empty input, because none of them has a defined value for zero observations. Only COUNT returns 0.
The trap
A consumer of this audit who treats the NULL luxury_total as a problem — wrapping the result in arithmetic, or comparing it to a numeric threshold — will get NULL on every category with no luxury products. NULL + anything is NULL; NULL > threshold is NULL, which WHERE treats as not-true. If the next step in the report needs a numeric zero for empty-set categories, the substitution has to be explicit at the point of use; the FILTER itself will not produce one. This is the same NULL-on-empty rule that governs every aggregate function on the platform, and FILTER does not override it.
You practiced COUNT FILTER returning 0 while SUM FILTER returns missing — the same condition produces different empty-set semantics: COUNT over zero matching records is 0; SUM over zero records is missing.
