Month with the Highest Revenue

Welcome to Excel Jet Consult blog post. In this SQL Server video tutorial, we are going to write the SQL query to find the month that has the highest revenue. Let’s get started.


The screenshot contains a simple select all statement to fetch all the records in the Monthly Sales table


Month with the Highest Revenue

To calculate the Month with the highest revenue, we are going to execute this query:

YEAR(TransactionDate) AS Year,
MONTH(TransactionDate) AS Month,
SUM(Revenue) AS TotalRevenue
[‘Monthly Sales$’]
TotalRevenue DESC;

  • Select the query and Execute

Query Explanation

We used the SELECT TOP 1 to instructs SQL to retrieve only the top result from the dataset. Then, we used YEAR(TransactionDate) AS Year and MONTH(TransactionDate) AS Month to extract the year and month values from the ‘TransactionDate’ column and provided Year and Month as the new column names. Furthermore, the SUM(Revenue) AS TotalRevenue is used calculate the sum of the ‘Revenue’ column for each group of records with the same year and month and provided ‘TotalRevenue’ as alias. The name of the table is [‘Monthly Sales$’]. Then, we used the GROUP BY YEAR(TransactionDate), MONTH(TransactionDate) clause to group the data based on the ‘Year’ and ‘Month’ extracted from the ‘TransactionDate’ column. Finally, we used ORDER BY TotalRevenue DESC clause sort the values from highest total revenue to lowest.

From the screenshot below, we can see that the highest revenue with the value of 74027.615 was recorded in June 2014.

See you in the next tutorial


Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *