Stored Procedure - duplication 1

[ClassyLinks]

Hi all!

I've got a stored procedure that is not behaving...or perhaps it is behaving, I just haven't trained it correctly.

What I'm TRYING to do is have a list of our top 10 selling items.

What is happening is that if an item sells more than once, it appears multiple times in the top 10 list.

You can see what I mean here:

http://www.ClassyThemes.com

(check out the "Most Popular Items" list towards the bottom of the page.

The stored procedure I'm using is:

SELECT TOP 10
Orders.OrderDate,
Orders.OrderID,
OrderDetails.ProductID,
SUM(OrderDetails.Quantity) as TotalNum,
Products.ModelName,
Products.ThemeDesigner

FROM
(OrderDetails
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID)
INNER JOIN Orders ON Orders.OrderID = OrderDetails.OrderID

WHERE OrderDetails.UnitCost > 1
AND datediff(day,Orders.OrderDate,getdate()) < 7
AND OrderDetails.OrderDownloaded = '1'

GROUP BY
Orders.OrderID,
Orders.OrderDate,
OrderDetails.ProductID,
Products.ModelName,
Products.ThemeDesigner

ORDER BY
Orders.OrderDate DESC, TotalNum DESC



Any ideas?? How can I get each item to appear only once? The select should be the OrderDetails.ProductID....I've tried DISTINCT, but that didn't work.

Any help would be greatly appreciated.

Cheers!

ClassyLinks ClassyLinks

 

[r937]

don't show results at the order level, summarize over all orders

SELECT TOP 10
SUM(OrderDetails.Quantity) as TotalNum,
Products.ModelName,
Products.ThemeDesigner
FROM
(OrderDetails
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID)
INNER JOIN Orders ON Orders.OrderID = OrderDetails.OrderID
WHERE OrderDetails.UnitCost > 1
AND datediff(day,Orders.OrderDate,getdate()) < 7
AND OrderDetails.OrderDownloaded = '1'
GROUP BY
Products.ModelName,
Products.ThemeDesigner
ORDER BY
SUM(OrderDetails.Quantity) DESC

rudy

http://rudy.ca/

 

[ClassyLinks]

Thanks Sooooooooo Much!

That did the trick!

Cheers! ClassyLinks