SQL ROLLUP with COALESCE-CodePudding

The data:

CREATE TABLE [dbo].[Purchase_Items]
(
    [ID] [int] NULL,
    [Model_Name] [varchar](50) NULL,
    [Brand] [varchar](50) NULL,
    [Price] [decimal](18, 2) NULL,
    [PurchaseDate] [date] NULL
);

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (1, N'Galaxy S10', N'Samsung', CAST(25000.00 AS Decimal(18, 2)), CAST(N'2020-01-12' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (2, N'Galaxy S20', N'Samsung', CAST(55000.00 AS Decimal(18, 2)), CAST(N'2020-01-18' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (3, N'Galaxy Note10', N'Samsung', CAST(40000.00 AS Decimal(18, 2)), CAST(N'2020-01-22' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (4, N'Galaxy A51', N'Samsung', CAST(21000.00 AS Decimal(18, 2)), CAST(N'2020-01-31' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (5, N'iPhone X', N'Apple', CAST(50000.00 AS Decimal(18, 2)), CAST(N'2020-02-03' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (6, N'iPhone 11', N'Apple', CAST(70000.00 AS Decimal(18, 2)), CAST(N'2020-02-07' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (7, N'iPhone 6s', N'Apple', CAST(20000.00 AS Decimal(18, 2)), CAST(N'2020-02-10' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (8, N'iPhone 8', N'Apple', CAST(30000.00 AS Decimal(18, 2)), CAST(N'2020-02-21' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (9, N'Y8', N'Huawei', CAST(16000.00 AS Decimal(18, 2)), CAST(N'2020-03-10' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (10, N'P30', N'Huawei', CAST(45000.00 AS Decimal(18, 2)), CAST(N'2020-03-12' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (11, N'Y9', N'Huawei', CAST(24000.00 AS Decimal(18, 2)), CAST(N'2020-03-14' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (12, N'P40', N'Huawei', CAST(50000.00 AS Decimal(18, 2)), CAST(N'2020-03-19' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (13, N'Redmi 9', N'Xiaomi', CAST(10000.00 AS Decimal(18, 2)), CAST(N'2020-03-21' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (14, N'Redmi K30', N'Xiaomi', CAST(25000.00 AS Decimal(18, 2)), CAST(N'2020-04-03' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (15, N'Mi Note 10', N'Xiaomi', CAST(15000.00 AS Decimal(18, 2)), CAST(N'2020-04-21' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (16, N'BlackShark', N'Xiaomi', CAST(30000.00 AS Decimal(18, 2)), CAST(N'2020-04-20' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (17, N'iPhone 11', N'Apple', CAST(70000.00 AS Decimal(18, 2)), CAST(N'2020-04-22' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (18, N'iPhone 11', N'Apple', CAST(70000.00 AS Decimal(18, 2)), CAST(N'2020-05-03' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (19, N'Galaxy S20', N'Samsung', CAST(55000.00 AS Decimal(18, 2)), CAST(N'2020-05-10' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (20, N'Galaxy S10', N'Samsung', CAST(25000.00 AS Decimal(18, 2)), CAST(N'2020-05-12' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (21, N'Galaxy Note10', N'Samsung', CAST(40000.00 AS Decimal(18, 2)), CAST(N'2020-05-14' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (22, N'iPhone 8', N'Apple', CAST(30000.00 AS Decimal(18, 2)), CAST(N'2020-05-21' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (23, N'BlackShark 3', N'Xiaomi', CAST(30000.00 AS Decimal(18, 2)), CAST(N'2020-05-22' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (24, N'P40', N'Huawei', CAST(45000.00 AS Decimal(18, 2)), CAST(N'2020-05-08' AS Date));

INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate]) 
VALUES (25, N'iPhone X', N'Apple', CAST(70000.00 AS Decimal(18, 2)), CAST(N'2020-05-10' AS Date));

The query:

SELECT 
    MONTH(PurchaseDate) AS Months, 
    COALESCE(Brand, 'Monthly Total') AS 'Brand', 
    SUM(Price) AS TotalAmount 
FROM 
    Purchase_Items 
GROUP BY 
    ROLLUP(PurchaseDate, Brand);

The output I get is like this:

It gives null every individual brand item not grouped, and I want the last null to be 'Grand Total' not 'Monthly Total'. I tried putting PurchaseDate or Brand column in and out the ROLLUP but it doesn't seem to work for the Brand to be grouped.

My desired output would be this. Compute all the item purchased per brand and per month, as well as the grand total of all purchased items in all date which I mentioned earlier

CodePudding user response：

Just use MONTH(PurchaseDate) in ROLLUP state:

SELECT 
    MONTH(PurchaseDate) AS Months, 
    COALESCE(Brand, 'Monthly Total') AS 'Brand', 
    SUM(Price) AS TotalAmount 
FROM 
    Purchase_Items 
GROUP BY 
    ROLLUP(MONTH(PurchaseDate), Brand);

CodePudding user response：

This could be done using CASE WHEN structure with GROUPING as follows:

SELECT 
    MONTH(PurchaseDate) AS Months, 
    CASE WHEN GROUPING(Brand) = 0 THEN Brand 
         When GROUPING(MONTH(PurchaseDate)) = 1 THEN 'Grand Total' 
         When GROUPING(Brand) = 1 THEN 'Monthly Total' END AS 'Brand',
    SUM(Price) AS TotalAmount 
FROM 
    Purchase_Items 
GROUP BY MONTH(PurchaseDate), Brand WITH ROLLUP;