MySQL COUNT query - help needed please

Can someone help with my "Best Sellers" query?

[rycrostud]

Hi,

I'm trying to write a MySQL query that will return the top selling products for an ecommrce site I'm working on.

The following query works and returns all the products that have been sold. The problem I'm having is adding a "COUNT" condition to the query so that the results will tell me the total number of each product sold so I can then create a "Top 10" best sellers list.

Any ideas?

SELECT
 Orders.ID,
Orders.Status,
OrderItems.OrderID,
OrderItems.ProductID,
ProductAttributes.ProductID,
ProductAttributes.LanguageID,
ProductAttributes.ShortTitle
FROM
Orders,
OrderItems,
ProductAttributes
WHERE
Orders.Status = 'CLOSED'
AND
Orders.ID = OrderItems.OrderID
AND
OrderItems.ProductID = ProductAttributes.ProductID
AND
ProductAttributes.LanguageID = '1'

[Fischerlaender]

This _should_ work. But be aware, it's just a quick guess.

SELECT 
Orders.ID, 
Orders.Status, 
OrderItems.OrderID, 
OrderItems.ProductID, 
ProductAttributes.ProductID, 
ProductAttributes.LanguageID, 
ProductAttributes.ShortTitle,
COUNT(*) AS howmany 
FROM 
Orders, 
OrderItems, 
ProductAttributes 
WHERE 
Orders.Status = 'CLOSED' 
AND 
Orders.ID = OrderItems.OrderID 
AND 
OrderItems.ProductID = ProductAttributes.ProductID 
AND 
ProductAttributes.LanguageID = '1' GROUP BY ProductAttributes.ProductID ORDER BY howmany DESC

[rycrostud]

Thanks for your help. After a bit of dabbling here's the final working code (I forgot to take quantities into account!)

SELECT
 Orders.ID,
Orders.Status,
OrderItems.OrderID,
OrderItems.ProductID,
SUM(OrderItems.Quantity) as TotalOrdered,
ProductAttributes.ProductID,
ProductAttributes.LanguageID,
ProductAttributes.ShortTitle
FROM
Orders,
OrderItems,
ProductAttributes
WHERE
Orders.Status = 'CLOSED'
AND
Orders.ID = OrderItems.OrderID
AND
OrderItems.ProductID = ProductAttributes.ProductID
AND
ProductAttributes.LanguageID = '1'
GROUP BY
OrderItems.ProductID
ORDER BY
TotalOrdered DESC
LIMIT
0, 10