CODEWITHSUNDEEP
sqlsql-server
DoktorAgon
DoktorAgon

Reputation: 69

List of products query - Adding the consecutive years condition

Stacks! I have a litlle problem with one of my queries. I have a sql query, that shows me list of most popular categories of products overall. It looks like this: 

    SELECT CategoryName, SUM(Quantity) as TotalQuantity
FROM ([Order Details]
INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
INNER JOIN Categories ON Products.ProductID = Categories.CategoryID)
GROUP BY CategoryName
ORDER BY SUM(Quantity) DESC;

Now, I need to modify it, so it will show me list of categories for each consecutive years. It should cover years 199-1998. I don't want to make a query for each year separately. Is there a way to change this query to show me list of categories for each year?

Upvotes: 0

Views: 99

Answers (2)

Wei Lin
Wei Lin

Reputation: 3811

SELECT CategoryName,datepart(yyyy, [YoutDateCol]) [date], SUM(Quantity) as TotalQuantity
FROM ([Order Details]
INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
INNER JOIN Categories ON Products.ProductID = Categories.CategoryID)
GROUP BY CategoryName,datepart(yyyy, [YoutDateCol])
ORDER BY SUM(Quantity) DESC;

Upvotes: 2

DEEPAK LAKHOTIA
DEEPAK LAKHOTIA

Reputation: 993

This should do the job for you:

SELECT Year(OrderDate), CategoryName,  SUM(Quantity) as TotalQuantity
FROM ([Order Details]
INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
INNER JOIN Categories ON Products.ProductID = Categories.CategoryID)
GROUP BY Year(OrderDate), CategoryName
ORDER BY TotalQuantity DESC;

Also, add a filter for date if you're only looking to capture data for specific years. Hope it helps.

Upvotes: 2

Related Questions