MySQL COUNT query - help needed please

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rycrostud
Forum Newbie
Posts: 1
Joined: Fri Aug 29, 2003 3:27 am

MySQL COUNT query - help needed please

Post 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'
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
Post Reply