Hi, I’m looking for some help on forming a query to give me the following results:
I have two tables as: product, orders that have product_id as linked field.
Product Table:
product_id
product_name
Order Table:
order_id
product_id
order_amount
Now I want to retrieve top 5 best selling products that are sold most. So I need to count the total number of records in orders table for each product and pick 5 best selling products. The query should return product_name and total quantity sold (record count).
SELECT p.*, COUNT(1) AS sales FROM products AS p INNER JOIN orders AS o ON p.product_id = o.product_id GROUP BY p.product_id ORDER BY COUNT(1) DESC LIMIT 5;
if you’re grouping on the PK and there are no other tables involved, then there’s no need for a GROUP BY clause at all
if no other tables are involved and you’re not grouping on the PK, chances are the “select star” leads to unpredictable output
if you’re grouping on the PK and there are other tables involved, then they will be in a one-to-many or many(one)-to-one relationship with the table that has the PK you’re grouping on, and in the latter case case there is no need for the GROUP BY, but in the former case it can lead to unmitigated disasters in unpredictable output
trust me, i’ve seen hundreds of cases where it’s a disaster, and only a few where it all (fortuitously) works out okay
In my experience, I’ve never seen issues if you do something like
select table.*, … other aggregate data … from table, etc group by table.pk.
And I’m pretty certain you need the group by in this case if you’re joining on another table. How would mysql implicitly know that you want to group your data?
