SQL
Perform Conditional Aggregation for Reporting
Generate dynamic reports by conditionally summing or counting values within a single SQL query using CASE statements, ideal for dashboards.
SELECT
product_category,
SUM(CASE WHEN order_status = 'completed' THEN total_amount ELSE 0 END) AS total_completed_sales,
COUNT(CASE WHEN order_status = 'pending' THEN 1 ELSE NULL END) AS total_pending_orders
FROM
orders
GROUP BY
product_category;
How it works: This snippet shows a powerful technique for creating flexible reports using conditional aggregation. By embedding `CASE` statements within aggregate functions like `SUM()` or `COUNT()`, you can calculate different metrics based on specific conditions within the same query. Here, it calculates the total sales for 'completed' orders and the count of 'pending' orders, grouped by `product_category`. This avoids multiple subqueries or application-level logic to achieve complex summarized data for dashboards or analytics.