SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Urgent Help Needed - Sql Query Formation

    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

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    What do you have so far?

  3. #3
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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;

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    just as a matter of basic query hygiene, one should ~never~ mix GROUP BY with the dreaded, evil "select star"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does that still apply when you're selecting table.* from the table you're grouping on?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by transio View Post
    How would mysql implicitly know that you want to group your data?
    implicitly? whoa, it's not ~that~ smart

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •