sitejerk — 2011-03-08T00:09:28-05:00 — #1
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.
- 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?
guido2004 — 2011-03-08T04:01:51-05:00 — #2
system — 2011-03-08T07:51:52-05:00 — #3
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 — 2011-03-08T09:32:43-05:00 — #4
just as a matter of basic query hygiene, one should ~never~ mix GROUP BY with the dreaded, evil "select star"
system — 2011-03-08T12:58:47-05:00 — #5
Does that still apply when you're selecting table.* from the table you're grouping on?
r937 — 2011-03-08T13:43:32-05:00 — #6
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
system — 2011-03-09T00:44:13-05:00 — #7
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 — 2011-03-09T07:01:12-05:00 — #8
implicitly? whoa, it's not ~that~ smart