SQL

Conditional Aggregation for Cross-Tabulation Reports

Learn to create powerful cross-tabulation reports or pivot tables using conditional aggregation with CASE statements, summarizing data across different categories efficiently for analytics.

SELECT
    EXTRACT(YEAR FROM order_date) AS order_year,
    SUM(CASE WHEN region = 'North' THEN total_amount ELSE 0 END) AS total_north_sales,
    SUM(CASE WHEN region = 'South' THEN total_amount ELSE 0 END) AS total_south_sales,
    SUM(CASE WHEN region = 'East' THEN total_amount ELSE 0 END) AS total_east_sales,
    SUM(CASE WHEN region = 'West' THEN total_amount ELSE 0 END) AS total_west_sales,
    SUM(total_amount) AS total_sales_all_regions
FROM
    orders
GROUP BY
    order_year
ORDER BY
    order_year;

How it works: This snippet demonstrates conditional aggregation, a technique to "pivot" data without using a specific `PIVOT` keyword (which isn't universally supported or always flexible). It calculates the sum of `total_amount` for different regions within each year using `CASE` statements inside `SUM()`. If a row matches a specific region, its `total_amount` is included; otherwise, `0` is added. This effectively creates new columns for each region's sales, providing a cross-tabulated view, which is very useful for reporting and dashboards.

Conditional Aggregation for Cross-Tabulation Reports

Need help integrating this into your project?