I want to change the name of a value in a column that will be based on the value in another column with the same row.
I'm using a GROUP BY operator, using the CUBE keyword.
Here is the code I used to create the table:
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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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));
Here is my query:
SELECT month(PurchaseDate) Month,
CASE WHEN month(PurchaseDate) is null then CONCAT('Grand Total for ', Brand)
ELSE COALESCE(Brand, 'Monthly Subtotal') end AS Brand,
SUM(Price) as 'Total Amount'
FROM Purchase_Items
GROUP BY CUBE(month(PurchaseDate), Brand)
This is the output:
Purchase Month | Brand | Total Amount
---------------------------------------------------------
3 | Xiaomi | 10000.00
4 | Xiaomi | 70000.00
5 | Xiaomi | 30000.00
NULL | Grand Total for Xiaomi | 110000.00
2 | Apple | 170000.00
4 | Apple | 70000.00
5 | Apple | 170000.00
NULL | Grand Total for Apple | 410000.00
NULL | Grand Total for | 520000.00
1 | Monthly Subtotal | 250000.00
2 | Monthly Subtotal | 200000.00
3 | Monthly Subtotal | 70000.00
I want to change 'Grand Total for' to 'Grand Total' but I don't know how.
I was thinking if I could change it based on the highest number in the 'Total Amount' column since they share the same row, but maybe there are other ways to solve this problem.
How can I fix this?
Test something like
CASE WHEN month(PurchaseDate) IS NULL AND Brand IS NULL THEN 'Grand Total'
WHEN month(PurchaseDate) IS NULL THEN CONCAT('Grand Total for ', Brand)
ELSE COALESCE(Brand, 'Monthly Subtotal')
END AS Brand,
In 1st condition - maybe WHEN Brand IS NULL will be enough?