Saturday, February 3, 2024

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.