A Brightlane buyer created category ID 999 as a placeholder for items pending classification, expecting some products to be assigned to it soon. None have been assigned yet.
Write a query to return the total list price of all products currently assigned to category 999, in a single column named total_price.
Assumptions:
- The
productstable contains every product in Brightlane's catalogue. - No products currently have
category_id = 999— there are no prices to combine. - The result row's single value will be missing, not
0.
Output:
- A single row with one column,
total_price. The value will be missing.
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
SUM(price) AS total_price
FROM
products
WHERE
category_id = 999The shape
When WHERE eliminates every row before the aggregate runs, SUM returns NULL rather than 0. The result row exists — aggregates always produce exactly one row — but the value is the absence of one, because there were no numbers to add.
Clause by clause
FROM productsis the source set: every product in Brightlane's catalogue.WHERE category_id = 999filters first. Category999is a placeholder the buyer set up; no products have been assigned to it yet, so this filter removes every row. The row set the aggregate will see is empty.SUM(price)runs over the empty row set. With no values to add,SUMhas nothing to return. PostgreSQL's answer isNULL, not0. The result row has one column whose value is missing.AS total_pricelabels the column. The aggregate produces a row whether the filter matched anything or not; the alias names that column so the result is readable as a domain quantity.
Why this and not 0
A learner might expect "no prices added together" to mean "zero dollars total." It doesn't, and the difference is load-bearing. 0 means "I added these numbers and the answer came to zero." NULL means "I had no numbers to add at all." Those are different facts about the world. Brightlane's catalogue having $0 of category-999 inventory would imply free products in that category; Brightlane having no products in category 999 at all implies the category is unpopulated.
PostgreSQL maintains the distinction by returning NULL for SUM (and AVG, MAX, MIN) over an empty set. The only aggregate that returns 0 on empty input is COUNT. Counting zero things is still a real count; summing zero things is undefined.
The trap
The trap is reading NULL as either a query bug or as "the sum was zero." NULL here is the correct answer to a specific question: "what's the total of a column when no rows are eligible?" The answer is that there is no total.
The behavior of SUM over an empty set is the foundational fact: missing on purpose. When the audit query returns NULL, that's the filter telling the story the data-governance team needs to hear — category 999 has no products to total up.
You practiced an aggregate over an empty row set. The recurring shape any time a condition eliminates every row before the aggregate runs.
