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