In this video, we learn how to write a single SQL query to calculate Yearly, Quarterly, Monthly Totals.
The database is AdventureWorks Database from Microsoft - Table - SalesOrderHeader.
You can also create the below custom table with sample data -
CREATE TABLE [dbo].[Sales](
[SalesOrderID] [int] NOT NULL,
[OrderDate] [datetime] NOT NULL,
[SalesOrderNumber] [nvarchar](25) NOT NULL,
[CustomerID] [int] NOT NULL,
[SubTotal] [money] NOT NULL
)
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71774, CAST(N'2021-01-10T00:00:00.000' AS DateTime), N'SO71774', 29847, 880.3484)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71776, CAST(N'2021-02-21T00:00:00.000' AS DateTime), N'SO71776', 30072, 78.8100)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71780, CAST(N'2021-02-21T00:00:00.000' AS DateTime), N'SO71780', 30113, 38418.6895)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71782, CAST(N'2021-02-27T00:00:00.000' AS DateTime), N'SO71782', 29485, 39785.3304)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71783, CAST(N'2021-03-07T00:00:00.000' AS DateTime), N'SO71783', 29957, 83858.4261)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71784, CAST(N'2021-03-17T00:00:00.000' AS DateTime), N'SO71784', 29736, 108561.8317)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71796, CAST(N'2021-03-20T00:00:00.000' AS DateTime), N'SO71796', 29660, 57634.6342)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71797, CAST(N'2021-04-10T00:00:00.000' AS DateTime), N'SO71797', 29796, 78029.6898)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71815, CAST(N'2021-06-01T00:00:00.000' AS DateTime), N'SO71815', 30089, 1141.5782)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71816, CAST(N'2021-05-01T00:00:00.000' AS DateTime), N'SO71816', 30027, 3398.1659)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71831, CAST(N'2021-03-04T00:00:00.000' AS DateTime), N'SO71831', 30019, 2016.3408)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71832, CAST(N'2020-03-04T00:00:00.000' AS DateTime), N'SO71832', 29922, 35775.2113)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71845, CAST(N'2020-02-04T00:00:00.000' AS DateTime), N'SO71845', 29938, 41622.0511)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71846, CAST(N'2020-06-22T00:00:00.000' AS DateTime), N'SO71846', 30102, 2453.7645)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71856, CAST(N'2020-05-18T00:00:00.000' AS DateTime), N'SO71856', 30033, 602.1946)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71858, CAST(N'2020-05-18T00:00:00.000' AS DateTime), N'SO71858', 29653, 13823.7083)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71863, CAST(N'2021-05-18T00:00:00.000' AS DateTime), N'SO71863', 29975, 3324.2759)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71867, CAST(N'2021-01-18T00:00:00.000' AS DateTime), N'SO71867', 29644, 1059.3100)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71885, CAST(N'2020-01-08T00:00:00.000' AS DateTime), N'SO71885', 29612, 550.3860)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71895, CAST(N'2020-02-11T00:00:00.000' AS DateTime), N'SO71895', 29584, 246.7392)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71897, CAST(N'2020-02-25T00:00:00.000' AS DateTime), N'SO71897', 29877, 12685.8899)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71898, CAST(N'2020-05-25T00:00:00.000' AS DateTime), N'SO71898', 29932, 63980.9884)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71899, CAST(N'2021-05-25T00:00:00.000' AS DateTime), N'SO71899', 29568, 2415.6727)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71902, CAST(N'2021-06-30T00:00:00.000' AS DateTime), N'SO71902', 29929, 74058.8078)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71915, CAST(N'2021-06-30T00:00:00.000' AS DateTime), N'SO71915', 29638, 2137.2310)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71917, CAST(N'2020-06-30T00:00:00.000' AS DateTime), N'SO71917', 30025, 40.9045)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71920, CAST(N'2020-05-31T00:00:00.000' AS DateTime), N'SO71920', 29982, 2980.7929)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71923, CAST(N'2021-02-01T00:00:00.000' AS DateTime), N'SO71923', 29781, 106.5408)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71935, CAST(N'2021-02-11T00:00:00.000' AS DateTime), N'SO71935', 29531, 6634.2961)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71936, CAST(N'2021-02-02T00:00:00.000' AS DateTime), N'SO71936', 30050, 98278.6910)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71938, CAST(N'2021-03-11T00:00:00.000' AS DateTime), N'SO71938', 29546, 88812.8625)
GO
INSERT [dbo].[Sales] ([SalesOrderID], [OrderDate], [SalesOrderNumber], [CustomerID], [SubTotal]) VALUES (71946, CAST(N'2021-05-15T00:00:00.000' AS DateTime), N'SO71946', 29741, 38.9536)
GO
SQL Query to calculate the totals
SELECT
YEAR(OrderDate) AS OrderYear,
DatePart(Quarter,OrderDate) as OrderQuarter,
MONTH(OrderDate) AS OrderMonth,
SUM(SubTotal) AS Incomes
FROM dbo.Sales
GROUP BY
GROUPING SETS
(
YEAR(OrderDate),
(YEAR(OrderDate), DatePart(Quarter,OrderDate)),
(YEAR(OrderDate),MONTH(OrderDate)) , ()
)
Order By YEAR(OrderDate), DatePart(Quarter,OrderDate), MONTH(OrderDate)