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.

Dataset

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:

SELECT TOP 1
YEAR(TransactionDate) AS Year,
MONTH(TransactionDate) AS Month,
SUM(Revenue) AS TotalRevenue
FROM
[‘Monthly Sales$’]
GROUP BY
YEAR(TransactionDate),
MONTH(TransactionDate)
ORDER BY
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 *