How to ROLLUP Sub Total Count, Sum and Average values for each Hierarchy Level in SQL Server

How to calculate the ROLLUP Sub Totals and Grand Total Count, Sum and Average values for each Dimension Level in SQL Server
Scenario:

Suppose we have a sample table with Price values for different Hierarchy Levels like Region, Country, ProdGroup, ProdName, as shown below.

/* Drop the table if it arleady exists

DROP TABLE IF EXISTS [dbo].tbl_Sample;

*/

/* Create a Sample table */

CREATE TABLE [dbo].tbl_Sample (

    ProdID INT PRIMARY KEY,

    Region VARCHAR(50),

    Country VARCHAR(50),

    ProdGroup VARCHAR(50),

    ProdName VARCHAR(50),

    Price DECIMAL(10, 2)

);

/* Insert data into the sample table */

INSERT INTO [dbo].tbl_Sample (ProdID, Region, Country, ProdGroup, ProdName, Price)

VALUES

    (1, 'Europe', 'France', 'Electronics', 'Desktop', 900.00),

    (2, 'Asia', 'India', 'Appliances', 'Washing Machine', 1300.00),

    (3, 'Europe', 'Italy', 'Electronics', 'Tablet', 1000.00),

    (4, 'Asia', 'Japan', 'Appliances', 'Refrigerator', 1800.00),

    (5, 'Europe', 'France', 'Electronics', 'Laptop', 950.00),

    (6, 'Asia', 'India', 'Appliances', 'Microwave', 900.00),

    (7, 'Europe', 'Italy', 'Electronics', 'Desktop', 850.00),

    (8, 'Asia', 'Japan', 'Appliances', 'Washing Machine', 1400.00),

    (9, 'Europe', 'France', 'Furniture', 'Table', 450.00),

    (10, 'Asia', 'India', 'Furniture', 'Bed', 750.00),

    (11, 'Asia', 'India', 'Furniture', 'Chair', 250.00),

    (12, 'Europe', 'Italy', 'Furniture', 'Chair', 350.00),

    (13, 'Asia', 'Japan', 'Furniture', 'Table', 300.00),

    (14, 'Asia', 'Japan', 'Furniture', 'Desk', 400.00)

SELECT * FROM [dbo].tbl_Sample

Now can find the Sub Total for each Dimension (Country, ProdGroup) and Grand Total for the Main Level (Region).

/* Calculate Sub Total and Grand Totals for each Dimension Level using ROLLUP Function */

SELECT

    COALESCE(Region, '|Grand Total|') AS Region,

    COALESCE(Country, '|Total ' + Region + '|') AS Country,

    COALESCE(ProdGroup, '|Total ' + Country  + '|') AS ProdGroup,

    COALESCE(ProdName, '|Total ' + ProdGroup + '|') AS ProdName,

    COUNT(ProdID) AS TotalCount,

    SUM(Price) AS TotalPrice,

    CAST(AVG(Price) AS DECIMAL(10,2)) AS AveragePrice

FROM [dbo].tbl_Sample

GROUP BY ROLLUP (Region, Country, ProdGroup, ProdName);

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------