Working out most sold Item

[chet139]

Hi everyone,

 

I am trying to work amount the most sold Item. To make this question simple, lets assume I want the productId and quantity sold of the product in all orders. I hold the quantity of item sold, productid and orderid in an orderline table.

 

So the query I use below gives me a result but I know its not correct. The query also has joins so I can see product name etc - but thats not important:

 

SELECT orderline.productId, count(ordQuantity), ordQuantity, productMake, productModel from orderline, product where orderline.productId = product.productId group by orderline.productId.

 

So as I said, the result I get it not correct for example I know, in total product product '100' as been sold 17 times - thats 12 times on one order and 5 more - one on 5 orders. Totalling 17 items sold.

 

However the result of the count I get back is 6......now 6 is actually the amount of orders product 100 as been in. so what I need to know how can i actually count the NO.OF a particular product as sold...

 

 

I know this is alot so please ask for further information if needed

 

[chet139]

SELECT orderline.productId, sum(ordQuantity), productMake, productModel from orderline, product where orderline.productId = product.productId group by orderline.productId order by sum(ordQuantity) desc

 

This is what I needed.

 

I worked it out.

 

SOLVED