Open In App

How to Compare Product Sales By Month in SQL?

Improve
Improve
Like Article
Like
Save
Share
Report

A monthly sales report represents the state of sales activities in a company per month. It helps the sales team to move in the right direction. Whether you are a sales leader or manager, metrics are immensely important for your company’s success. If your data is stored in a database, you can calculate the monthly sales report using SQL. In this article, we are going to see how we can calculate monthly sales in SQL.

 1. GROUP BY Clause

 2. Aggregate Functions

Let’s first create our demo database.

Step 1: Creating the database

Create a new database named Product_details and then use that.

Query:

CREATE DATABASE Product_details; USE Product_details;

Output:

Step 2: Defining the table

Create a table named Products and add these two columns Order_date and Sales. 

Query:

CREATE Table Products ( Order_date date, Sales int); 

Output:

Step 3: Insert rows into the table and Insert these rows in the table.

Query:

INSERT INTO Products(Order_date,Sales)    
VALUES('2021-01-01',20),('2021-03-02',32),('2021-02-03',45), 
('2021-01-04',31),('2021-03-05',33),('2021-01-06',19),
('2021-04-07',21),('2021-03-08',10),('2021-02-09',40), 
('2021-03-10',20),('2021-03-11',26),('2021-04-12',22),      
('2021-04-13',10),('2021-01-14',28),('2021-03-15',15), 
('2021-01-16',12),('2021-04-17',10),('2021-02-18',18),  
('2021-04-19',14),('2021-01-20',16),('2021-02-21',12),
('2021-03-22',51),('2021-02-23',13),('2021-03-24',15),
('2021-02-25',30),('2021-03-26',14),('2021-04-27',16), 
('2021-02-28',15),('2021-01-29',20),('2021-01-30',18); 

Output:

Step 4: Viewing Inserted data

Query:

SELECT * FROM Products; 

Output:

Step 5: Now, let’s make our query to compare the product sales by month.

SQL Server provides MONTH and YEAR functions that allow us to find out the month and year respectively, from the given date. We will use these two functions, the GROUP BY function and SUM function to calculate the total sales.

Query:

SELECT YEAR(Order_date) AS Year, 
MONTH(Order_date) AS Month,SUM(Sales) 
AS Total_Sales FROM Products   
GROUP BY YEAR(Order_date), MONTH(Order_date) ; 

Here, we are simply grouping up the months and years using the GROUP BY clause and then getting the total sales using the SUM aggregate function.  

Output:

In the above query, we have used the SUM function to calculate the total sales every month. You can also find out the total count of sales every month. For that, replace the SUM function with the COUNT function.

Query:

SELECT YEAR(Order_date) AS Year,MONTH(Order_date) 
AS Month,COUNT(Sales) AS Count_Of_Sales     
FROM Products GROUP BY YEAR(Order_date),MONTH(Order_date); 

Output:

Query:

SELECT YEAR(Order_date) AS Year, DATENAME(MONTH, Order_date) 
 AS Month, COUNT(Sales) AS Count_Of_Sales FROM Products 
 GROUP BY YEAR(Order_date), DATENAME(MONTH, Order_date); 

Output:

The DATENAME() function returns a specific part of the date. Here, we used it to return the MONTH part of the Order_date string.

We can show this data in decreasing order using the ORDER BY clause.

Query:

SELECT YEAR(Order_date) AS Year, DATENAME(MONTH, Order_date) 
AS Month, COUNT(Sales) AS Count_Of_Sales FROM Products GROUP
BY YEAR(Order_date), DATENAME(MONTH, Order_date) ORDER 
BY Count_Of_Sales DESC; 

Output:

From this data report, we can easily tell that the count of sales was the highest in March. 

Now, you may want to calculate the monthly sales for different years. For that, you don’t have to change anything at all, the query will remain exactly the same. You can also do some experiments by using the WHERE clause to filter out some data or the other aggregate functions.


Last Updated : 16 Nov, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads