Welcome to Excel Jet Consult blog post. In this SQL Server video tutorial, we are going to learn how to calculate total sales by quarters grouped by Year. Let’s get started.
Dataset
The screenshot contains a simple select all statement to fetch all the records in the Monthly Sales table
Total Sales by Quarters Grouped By Year
To calculate Total Sales by Quarters Grouped By Year, we will execute this query:
SELECT
YEAR(TransactionDate) AS SalesYear,
DATEPART(QUARTER, TransactionDate) AS SalesQuarter,
SUM(Revenue) AS TotalSales
FROM [‘Monthly Sales$’]
GROUP BY
YEAR(TransactionDate),
DATEPART(QUARTER, TransactionDate)
ORDER BY
SalesYear,
SalesQuarter;
Select the Query and Execute
Query Explanation
The SELECT YEAR(TransactionDate) AS SalesYear, DATEPART(QUARTER, TransactionDate) AS SalesQuarter, SUM(Revenue) AS TotalSales selected three columns where SalesYear and SalesQuarter represent the aliases for the extracted year and quarter from the TransactionDate column. The TotalSales alias houses the sum of the Revenue column. The GROUP BY YEAR(TransactionDate), DATEPART(QUARTER, TransactionDate) group by clause groups the data based on the SalesYear and SalesQuarter that ensures that the sum of ‘Revenue’ is calculated separately for each unique combination of year and quarter. The ORDER BY SalesYear, SalesQuarter orders the results in ascending order by SalesYear and SalesQuarter. The name of the table is FROM [‘Monthly Sales$’]
From the screenshot below, we can see the TotalSales by Quarters grouped by Year
See you in the next video