Page 1 of 1

MySQL COUNT query - help needed please

Posted: Fri Aug 29, 2003 3:27 am
by 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?

Code: Select all

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'

Posted: Fri Aug 29, 2003 10:13 am
by JAM

Code: Select all

select 
 ShortTitle, count(sold_fieldname) -- name and the count
from 
 ProductAttributes
group by 
 ShortTitle -- you need group by
order by 
 count(sold_fieldname) desc -- sorting order
limit 10 -- how many results
Just a general idea. I might have written something ill'ish, but I think you'll get the point.