Best practise to find best sellers?

Hi,

I am struggling with something quite simple. I have 2 linked tables (orders and orderdetails) which store all the order information. The orders table holds the top level data (order number, order value etc) and orderdetails stores each order line.

What I want to do is find out the best selling products. The way I thought would be to select all order lines in SQL and then <CFLOOP> though each incrementing each time a specific product is sold. These seems cumbersome. Is there a way to do this just in SQL? Or a better way?

Thanks

 

select count(itemName) as timesPurchased, itemName
from orderdetails
order by timesPurchased desc
limit 10

Then you have the top 10 items that were sold.

itemName could be the itemID what ever make the item unique. If its an id you'll need an extra join to the itemsTable.

 

Hi Paul,

Thanks for the reply. I have created the following query:

select count(od.fkingredientid) as timesPurchased, i.title
from dorderdetails od, dingredients i
where od.fkingredientid = i.pkingredientid 
order by timesPurchased desc
limit 10

Code (markup):

The results aren't correct though. It displays this:

*timesPurchased*       *title*
23092	                Product A

Code (markup):

So its basically just counting all product sales, but not separating them for each different product.

Any help would be appreciated.

 

Try

Select item, count(1) as Purchases
from Yourtable
Group by Item
Order by count(1) desc

This will group your counts by items and sort accordingly

 

Thanks..