SQL

Conditional Aggregation with CASE Statements

Perform aggregate functions (like COUNT, SUM) on subsets of data based on specific conditions within a single query using CASE statements for flexible reporting.

SELECT
  product_category,
  COUNT(CASE WHEN order_date >= '2023-01-01' THEN order_id ELSE NULL END) AS orders_2023,
  COUNT(CASE WHEN order_date < '2023-01-01' THEN order_id ELSE NULL END) AS orders_before_2023,
  SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END) AS completed_sales
FROM orders
GROUP BY product_category;

How it works: This query uses `CASE` statements within aggregate functions to count or sum values based on different conditions in a single pass. For example, it counts orders placed in 2023 versus prior years and sums completed sales. This technique is highly effective for creating flexible summary reports without needing multiple separate queries or subqueries for each condition.

Conditional Aggregation with CASE Statements

Need help integrating this into your project?