SubTotals with SQL using ROLLUP

To create subtotals in SQL using the ROLLUP operator, you can follow a similar approach to the previous example, but this time, you can include the subtotals explicitly in the result using a CASE statement to identify the subtotal rows. Here's an example:

Assuming you have a table named `Sales` with columns `ProductCategory`, `ProductSubCategory`, and `Revenue`, and you want to calculate the sum of revenue at different hierarchy levels (Category and Subcategory) and include subtotals:

```sql

SELECT 
    CASE
        WHEN GROUPING(ProductCategory) = 1 THEN 'Total Category'
        WHEN GROUPING(ProductSubCategory) = 1 THEN 'Total Subcategory'
        ELSE ProductCategory
    END AS ProductCategory,
    CASE
        WHEN GROUPING(ProductSubCategory) = 1 THEN NULL
        ELSE ProductSubCategory
    END AS ProductSubCategory,
    SUM(Revenue) AS TotalRevenue
FROM
    Sales
GROUP BY
    ROLLUP (ProductCategory, ProductSubCategory)
ORDER BY
    GROUPING(ProductCategory),
    GROUPING(ProductSubCategory),
    ProductCategory,
    ProductSubCategory;

```

In this query:

- The `CASE` statements are used to determine whether a row represents a subtotal. If `GROUPING(ProductCategory) = 1`, it's a subtotal at the category level, and if `GROUPING(ProductSubCategory) = 1`, it's a subtotal at the subcategory level. Otherwise, it's a regular row.

- The `ORDER BY` clause is used to sort the result so that subtotal rows come before the detailed rows, and within subtotals, subcategory subtotals come before category subtotals.

Here's an example of what the result might look like:

In this result, you can see the subtotals at both the category and subcategory levels, and they are explicitly labeled as "Total Category" and "Total Subcategory."

You can adjust the query and columns as needed to fit your specific data and hierarchy structure. This approach allows you to create subtotals in your SQL query using the ROLLUP operator.