sql query - problem ordering results

[bewise]

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?

[wwb_99]

Yes. Look at the AVG aggregate function.

[bewise]

am I able to order by the AVG() function?

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

[longneck]

you can't use an aggregate function like AVG() without a GROUP BY clause.

[r937]

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