Urgent Help Needed - Sql Query Formation

[sitejerk]

Hi, I'm looking for some help on forming a query to give me the following results:

1. 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

2. 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).

Could someone help writing this query please?

TIA

[guido2004]

What do you have so far?

[transio]

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;

[r937]

just as a matter of basic query hygiene, one should ~never~ mix GROUP BY with the dreaded, evil "select star"

[transio]

Does that still apply when you're selecting table.* from the table you're grouping on?

[r937]

depends on whether you're grouping on the PK

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

[transio]

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?

[r937]

How would mysql implicitly know that you want to group your data?

implicitly? whoa, it's not ~that~ smart