Get the Total Sales for Each Product by Month SQL Server

Get the Total Sales for Each Product by Month Query Using MS SQL Server?

To get the total sales for each product by month, you need to aggregate sales data on a monthly basis for each product. This typically involves:

1. Extracting the month and year from the order date.
2. Grouping the sales data by product and month.
3. Summing the sales amounts for each product in each month.

Assuming you have the following tables:

• OrderDetails:
  • OrderID
  • ProductID
  • Quantity
  • UnitPrice (price per unit of the product)
• Orders:
  • OrderID
  • OrderDate

Here’s a SQL query to get the total sales for each product by month:

SQL Query

WITH MonthlySales AS (

    SELECT

        od.ProductID,

        DATEPART(YEAR, o.OrderDate) AS SalesYear,

        DATEPART(MONTH, o.OrderDate) AS SalesMonth,

        SUM(od.Quantity * od.UnitPrice) AS TotalSales

    FROM OrderDetails od

    JOIN Orders o ON od.OrderID = o.OrderID

    GROUP BY

        od.ProductID,

        DATEPART(YEAR, o.OrderDate),

        DATEPART(MONTH, o.OrderDate)

)

SELECT

    p.ProductID,

    p.ProductName,

    ms.SalesYear,

    ms.SalesMonth,

    ms.TotalSales

FROM MonthlySales ms

JOIN Products p ON ms.ProductID = p.ProductID

ORDER BY p.ProductID, ms.SalesYear, ms.SalesMonth;

Explanation:

1. CTE MonthlySales:

   • DATEPART(YEAR, o.OrderDate) AS SalesYear: Extracts the year from the OrderDate.
   • DATEPART(MONTH, o.OrderDate) AS SalesMonth: Extracts the month from the OrderDate.
   • SUM(od.Quantity * od.UnitPrice) AS TotalSales: Calculates the total sales amount for each product in each month.
   • GROUP BY od.ProductID, DATEPART(YEAR, o.OrderDate), DATEPART(MONTH, o.OrderDate): Groups by ProductID, year, and month to aggregate sales.

2. Final Query:

   • JOIN Products p ON ms.ProductID = p.ProductID: Joins with the Products table to get product names.
   • ORDER BY p.ProductID, ms.SalesYear, ms.SalesMonth: Orders the results by product and then by year and month.

Example Data and Results

Assume the OrderDetails and Orders tables have the following data:

OrderDetails Table:

OrderID	ProductID	Quantity	UnitPrice
1	1	10	100
2	1	5	100
3	2	20	200
4	1	15	100
5	2	10	200

Orders Table:

OrderID	OrderDate
1	2024-01-10
2	2024-01-15
3	2024-02-05
4	2024-02-10
5	2024-03-01

Products Table:

ProductID	ProductName
1	Widget A
2	Widget B

The query would produce:

ProductID	ProductName	SalesYear	SalesMonth	TotalSales
1	Widget A	2024	1	1500
1	Widget A	2024	2	1500
2	Widget B	2024	2	4000
2	Widget B	2024	3	2000

This result shows the total sales for each product, broken down by month.

You can execute this query in SQL Server Management Studio or any SQL query tool connected to your SQL Server to get the total sales for each product by month. Adjust the table and column names according to your actual database schema.