Welcome to Excel Jet Consult blog post. In this SQL Server video tutorial, we are going to learn how to calculate Average Monthly Revenue using Sub Query. Let’s get started.
Dataset
The screenshot contains a simple select all statement to fetch all the records in the Monthly Sales table
Average Monthly Revenue using Sub Query
To calculate the Average Monthly Revenue, execute the SQL query:
SELECT AVG(MonthlyRevenue) AS AverageMonthlyRevenue
FROM (
SELECT
YEAR(TransactionDate) AS Year,
MONTH(TransactionDate) AS Month,
SUM(Revenue) AS MonthlyRevenue
FROM [‘Monthly Sales$’]
GROUP BY YEAR(TransactionDate), MONTH(TransactionDate)
) AS MonthlyTotals;
Select the Query and Execute
Query Explanation
Inner Query (Subquery)
We used SELECT YEAR(TransactionDate) AS Year, MONTH(TransactionDate) AS Month, SUM(Revenue) AS MonthlyRevenue to extract the year and month from the TransactionDate column and calculated the sum of Revenue for each unique combination of year and month. In addition, we used GROUP BY YEAR(TransactionDate), MONTH(TransactionDate) to group the data by year and month which will ensure that the sum of Revenue is calculated separately for each combination. The data was selected from this table [‘Monthly Sales$’]
Outer Query:
The SELECT AVG(MonthlyRevenue) AS AverageMonthlyRevenue takes the result of the inner query (the monthly revenue for each period) and calculated the average of the monthly revenues. The ‘AverageMonthlyRevenue.’ is the new column name provided for the result.
The screenshot below shows the AverageMonthlyRevenue to be 24928.0309
