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 ShopTable · products9 columns · 24 rows

Table · products

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

9 columns · 24 rows

product_id intproduct_name textcategory_id intsupplier_id intprice decimalcost decimalquantity_on_hand intreorder_level intdiscontinued bool

Your task

Build a per-category stock report. For each category returncategory_name, the total products, how many areavailable (not discontinued), how many arediscontinued, and the total on_hand quantity. Order by category name.

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

Expected answer