SQL
Generate Conditional Summary Report with CASE
Create flexible summary reports by conditionally counting or summing values based on specific criteria within a single SQL query using CASE statements.
SELECT
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_orders,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders,
COUNT(*) AS total_orders
FROM
orders;
How it works: This powerful SQL technique uses `CASE` statements inside aggregate functions (`SUM`) to perform conditional counting. It allows you to count occurrences of different statuses (e.g., 'completed', 'pending', 'cancelled') from the `orders` table in a single row result. Each `SUM(CASE WHEN ... THEN 1 ELSE 0 END)` expression effectively acts as a conditional counter for its specific status, while `COUNT(*)` gives the overall total.