CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ayman
Ayman

Reputation: 872

SQL query to get product sales by category for each month

Hi i am looking for a query to get sales for each products category for each month

i have 2 categories 

Catid CatName
1     Dispenser
2     Refill

i used this below query to get total sales for all products categories

SELECT YEAR(OrderDate) as [Sales Year],
MONTH(OrderDate) as [Sales Month],
SUM(TotalAmount) AS [Total Sales]
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY YEAR(OrderDate), MONTH(OrderDate)

out put for above query

Sales Year  Sales Month        Total Sales  
2015            6              1000.00  
2015            7              2000.00

how can i get total sales for each product category

out put should be like this

   Sales Year   Sales Month        Dispenser-sales      Refill-sales         
    2015            6              500.00                 500.00      
    2015            7              500.00                 1500.00

my schema

Schema

Upvotes: 0

Views: 15455

Answers (2)

JamieD77
JamieD77

Reputation: 13949

You might want to do this using dynamic sql and PIVOT so you won't have to know the catid catname combinations.

Plus you probably want to sum the Orderdetails Quantity * UnitPrice in order to get the individual category amounts, instead of the Order total amount.

DECLARE @SQL VARCHAR(MAX),
        @Columns VARCHAR(MAX)
SELECT @Columns = COALESCE(@Columns + ',', '') + QUOTENAME(CatName)
FROM Category

SET @Sql = '
    SELECT * FROM
    (
        SELECT  YEAR(o.OrderDate) [Sales Year],
                MONTH(o.OrderDate) [Sales Month],
                c.CatName,
                od.Quantity * od.UnitPrice AS [CatAmount]
        FROM    Orders o
                JOIN OrderDetails od ON o.Order_ID = od.Orderid
                JOIN Store s ON od.Proid = s.Pro_ID
                JOIN Category c ON s.CatID = c.Catid
    ) T
    PIVOT
    (
        SUM([CatAmount])
        FOR CatName IN (' + @Columns + ')
    ) p
    ORDER BY [Sales Year], [Sales Month]'

EXEC(@Sql)

If you just want specific categories, you can just hard code the names into your pivot statement without using dynamic sql.

SELECT * FROM
(
    SELECT  YEAR(o.OrderDate) [Sales Year],
            MONTH(o.OrderDate) [Sales Month],
            c.CatName,
            od.Quantity * od.UnitPrice AS [CatAmount]
    FROM    Orders o
            JOIN OrderDetails od ON o.Order_ID = od.Orderid
            JOIN Store s ON od.Proid = s.Pro_ID
            JOIN Category c ON s.CatID = c.Catid
) T
PIVOT
(
    SUM([CatAmount])
    FOR CatName IN ([Dispenser-sales],[Refill-sales])
) p
ORDER BY [Sales Year], [Sales Month]

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You have to join the other tables involved (based on the schema shown) and use conditional aggregation, to get a split by category id.

SELECT 
YEAR(o.OrderDate) as [Sales Year],
MONTH(o.OrderDate) as [Sales Month],
SUM(case when c.catid = 1 then o.TotalAmount else 0 end) AS [Dispenser Sales],
SUM(case when c.catid = 2 then o.TotalAmount else 0 end) AS [Refill Sales]
FROM Orders o
join orderdetails od on o.order_id = od.orderid
join store s on s.pro_id = od.pro_id
join category c on c.catid = s.catid
GROUP BY YEAR(o.OrderDate), MONTH(o.OrderDate)
ORDER BY YEAR(o.OrderDate), MONTH(o.OrderDate)

Upvotes: 1

Related Questions