SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sql query - problem ordering results

    Hello all,

    I have a db table that holds details about phones (PhoneID, Name). In another table, I have a db table that will hold customer reviews about each of these phones. (PhoneID, Rating, Review). THe Review db table will contain multiple reviews for the same phone.

    What im looking to do is return all phones, ordered by the Rating. (i.e. the average rating for each phone will need to be found first, before I can order by this rating)

    Is there any way of achiving this by using only 1 db access?

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Yes. Look at the AVG aggregate function.

  3. #3
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    am I able to order by the AVG() function?

    Code:
    SELECT     *
    FROM         Phones AS p INNER JOIN
                          ReviewDetails AS r ON r.ReviewTypeID = p.PhoneID
    ORDER BY AVG(r.ReviewTypeID)
    im getting an error at the moment

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you can't use an aggregate function like AVG() without a GROUP BY clause.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT p.PhoneID
         , p.Name
         , AVG(r.Review) as rating
      FROM Phones AS p 
    INNER 
      JOIN ReviewDetails AS r 
        ON r.ReviewTypeID = p.PhoneID
    GROUP
        BY p.PhoneID
         , p.Name
    ORDER 
        BY rating desc
    r937.com | rudy.ca | 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
  •