CODEWITHSUNDEEP
mysql
user1001176
user1001176

Reputation: 1165

counting the most sold products from mysql

I am trying to get the most sold products list by making a mysql query . The problem is its still getting all of the data even after i use count . 

select 
    mf.*,
    od.*,
    count(od.product_id) as product_count
from masterflight mf , 
    order_details od 
where od.product_id=mf.ProductCode 
group by od.product_id 
order by product_count

Here masterflight is the table where the product details are stored with their ids . And order_details is the table where record of each product being sold individually are stored . What i was trying to put in a logic that suppose a product with id 2 is sold 4 times and each time it has a separate entry then i would count those using COUNT and then display it which does it seems to be working .

Upvotes: 1

Views: 188

Answers (1)

Eric
Eric

Reputation: 95223

Try something a little neater:

select
    mf.ProductCode,
    count(od.*) as product_count
from
    order_details od
    inner join masterflight mf on
        od.product_id = mf.ProductCode
group by
    mf.ProductCode
order by product_count desc

The problem is that you're selecting all of od, but you're not grouping by it, so you're just getting all of the order rows, which doesn't help you really at all. I should note that MySQL is the only one of the major RDBMSes that allows that behavior--and it's confusing and tough to debug. I'd advise against using that particular feature. As a general rule, if you've selected a column but don't have an aggregate (e.g.-sum, avg, min, etc.) on it, then you need it in the group by clause.

Upvotes: 1

Related Questions