API tools faq
paste
Advertisement
RockField64

AnotherRollup

RockField64
Nov 11th, 2021
194
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 5.07 KB | None | 0 0
raw download clone embed print report
  1. DROP TABLE IF EXISTS Purchase_Items;
  2. CREATE TABLE [dbo].[Purchase_Items](
  3.     [ID] [int] NULL,
  4.     [Model_Name] [varchar](50) NULL,
  5.     [Brand] [varchar](50) NULL,
  6.     [Price] [decimal](18, 2) NULL,
  7.     [PurchaseDate] [date] NULL
  8. );
  9. INSERT [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));
  10. INSERT [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));
  11. INSERT [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));
  12. INSERT [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));
  13. 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));
  14. 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));
  15. 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));
  16. 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));
  17. INSERT [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));
  18. INSERT [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));
  19. INSERT [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));
  20. INSERT [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));
  21. 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));
  22. 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));
  23. 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));
  24. 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));
  25. 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));
  26. 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));
  27. INSERT [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));
  28. INSERT [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));
  29. INSERT [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));
  30. 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));
  31. 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));
  32. INSERT [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));
  33. 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));
  34.  
  35. SELECT * FROM Purchase_Items
  36.  
  37.  
  38. SELECT COALESCE(Brand, 'Grand Total') Brand,
  39. SUM (Price) AS 'Total Amount'
  40. FROM Purchase_Items
  41. GROUP BY ROLLUP (Brand)
  42.  
  43. SELECT ID AS 'Month',
  44. COALESCE (Brand, 'Monthly Total') Brand,
  45. SUM (Price) AS 'Total Amount'
  46. FROM Purchase_Items
  47. GROUP BY ROLLUP (ID, Brand)
  48.  
  49.  
  50.  
  51.  
  52.  
  53.  
  54.  
  55.  
  56.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!