SQL
Perform Conditional Aggregation with SQL CASE Statements
Generate powerful summary reports by conditionally counting or summing values using CASE expressions within aggregate functions, perfect for pivoting data or complex metrics.
SELECT
category_name,
COUNT(CASE WHEN p.status = 'available' THEN p.product_id END) AS available_products,
COUNT(CASE WHEN p.status = 'outofstock' THEN p.product_id END) AS out_of_stock_products,
SUM(CASE WHEN p.price > 100 THEN 1 ELSE 0 END) AS high_value_products_count
FROM
products p
JOIN
categories c ON p.category_id = c.category_id
GROUP BY
category_name
ORDER BY
category_name;
How it works: This query demonstrates conditional aggregation using `CASE` statements within aggregate functions like `COUNT` and `SUM`. Instead of performing multiple queries or complex joins, you can create multiple conditional counts or sums in a single pass over the data. For example, it counts 'available' and 'out of stock' products separately for each category, and also sums a flag to count 'high value' products, effectively creating a simple pivot table directly in the SELECT clause.