Brightlane's inventory team wants a single-row price comparison:
- Average price of in-stock products (
stock_qty > 0). - Average price of out-of-stock products (
stock_qty = 0).
Write a query to return both averages in a single row.
Assumptions:
- The
productstable contains every product in the catalogue. - Each
AVG(CASE ...)computes the average over only the rows that match the condition — the unmatched rows returnNULLfromCASEandAVGignoresNULLin both the numerator and denominator.
Output:
- A single row with two columns,
avg_instock_priceandavg_outofstock_price.
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
AVG(
CASE
WHEN stock_qty > 0 THEN price
END
) AS avg_instock_price,
AVG(
CASE
WHEN stock_qty = 0 THEN price
END
) AS avg_outofstock_price
FROM
productsThe shape
AVG(CASE WHEN cond THEN price END) returns the average price over only the rows where cond is true. The out-of-stock average comes back as NULL because Brightlane has no products with stock_qty = 0 in the catalogue right now — the CASE returned NULL for every row, and AVG over an empty set is itself NULL.
Clause by clause
AVG(CASE WHEN stock_qty > 0 THEN price END) AS avg_instock_priceevaluates theCASEonce per product. Wherestock_qtyis greater than0, theCASEreturns that product'sprice; otherwise it falls through with noELSEand returnsNULL.AVGskips theNULLs on both sides of the division — they are not added to the sum, and they are not counted in the denominator — so the result is the average price across the in-stock subset,326.58....AVG(CASE WHEN stock_qty = 0 THEN price END) AS avg_outofstock_pricedoes the same against the opposite predicate. Every row falls through toNULL, the input set forAVGis empty, and the result isNULL. Not0—0would mean "I averaged some prices and the answer was zero," which is a different claim.NULLmeans "there was nothing to average."FROM productsis the source set. The query is not grouped: the two averages are computed across the whole catalogue in a single pass, with theCASEdoing the per-row routing into the right bucket.
Why this and not SUM / COUNT manually
A hand-rolled SUM(CASE) / COUNT(CASE) produces the same number when there's data to average, but it has two failure modes AVG avoids. On an empty input set, the manual division is zero divided by zero, which raises a division-by-zero error rather than returning NULL. And on an integer column, SUM / COUNT runs integer division and truncates the fractional part — the N013-H2 trap that AVG's automatic type-widening sidesteps.
You practiced AVG(CASE) for conditional averaging. The recurring rule: AVG excludes NULL from both the sum and the count, so an AVG(CASE WHEN cond THEN col END) is exactly the average of col over the rows where cond is true — as if the other rows didn't exist.
