Lesson 16.1

Pivoting with Conditional Aggregation in SQL

A pivot answers several sub-questions per group at once. Each becomes its own aggregate with a FILTER condition.

A plain GROUP BY gives you one number per group. A pivot gives you several: one column for each condition, by putting a FILTER on each aggregate. It's how a single query becomes a small report.

count(*) filter (where …) counts only the rows matching that condition, so you can count "available" and "discontinued" side by side while still grouping once per category.

Pattern

select region,
       count(*) filter (where status = 'won')  as won,
       count(*) filter (where status = 'lost') as lost
from deals
group by region

On databases without FILTER (SQL Server, MySQL), the same idea is written as sum(case when … then 1 else 0 end). DuckDB supports both; FILTER reads more cleanly.

Schema · Garden Shop Tables · products, categories 2 tables

Table · products

One row per product, with price, cost, and inventory levels.

9 columns · 24 rows

product_id int product_name text category_id int supplier_id int price decimal cost decimal quantity_on_hand int reorder_level int discontinued bool

Table · categories

Lookup table of product categories.

2 columns · 8 rows

category_id int category_name text

Your task

Build a per-category stock report. For each category return category_name, the total products, how many are available (not discontinued), how many are discontinued, and the total on_hand quantity. Order by category name.