SQL
Count Items by Status Using Conditional Aggregation
Efficiently count records for different categories or statuses within a single SQL query using CASE statements inside aggregate functions for clearer reporting.
SELECT
product_category,
COUNT(CASE WHEN status = 'available' THEN 1 END) AS available_products,
COUNT(CASE WHEN status = 'out_of_stock' THEN 1 END) AS out_of_stock_products,
COUNT(CASE WHEN status = 'discontinued' THEN 1 END) AS discontinued_products
FROM
products
GROUP BY
product_category;
How it works: This query uses `CASE` expressions within `COUNT()` to conditionally count products based on their `status` for each `product_category`. This allows fetching multiple aggregate counts (e.g., available, out-of-stock, discontinued) in a single row per category, making it efficient for reporting and dashboard views.