Generate Multi-Level Aggregations with SQL GROUPING SETS

SELECT
    COALESCE(category, 'All Categories') AS category,
    COALESCE(region, 'All Regions') AS region,
    SUM(sales_amount) AS total_sales
FROM
    Sales
GROUP BY GROUPING SETS (
    (category, region), -- Aggregation by category and region
    (category),         -- Aggregation by category only
    (region),           -- Aggregation by region only
    ()                  -- Aggregation for the grand total
)
ORDER BY
    category, region;

How it works: The `GROUPING SETS` clause in SQL allows you to specify multiple grouping options within a single `GROUP BY` statement. Instead of running separate queries for different aggregation levels (e.g., total sales per category and total sales per region), you can combine them into one, which often improves performance and simplifies application logic. This example calculates sales aggregated by (category, region), by category only, by region only, and a grand total across all data. `COALESCE` is used to replace NULLs (which `GROUPING SETS` produces for suppressed grouping columns) with more descriptive strings.