Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Oct 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question MYSQL JOIN and AVG problem

    Hello!!

    I have a prob with Mysql.

    I got two tables

    First : material with fields 'id' and 'name'
    Second: ratings with fields 'material_id', 'comment' and 'rating'

    Now I want to select all material with the average rating associated.

    So I did
    "SELECT m.id,AVG(b.rating) FROM material as m LEFT JOIN bewertungen as b ON m.id = b.materialid AND b.rating > 0 GROUP BY id" .

    It works so far as it returns all material_ids associated with the average rating for this material or NULL if no rating is present in the ratings table.

    Now I want to sort the results by AVG(rating) but i cannot add "ORDER BY AVG(rating)" as this results in an error.

    Can anyone help me, please?

    Greetings
    Fabian Koenig

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    frankfurt, german banana republic
    Posts
    1,848
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try to use an alias for "AVG(b.rating)", like "AVG(b.rating) AS averages" and sort on that by using "ORDER BY averages".

  • #3
    New to the CF scene
    Join Date
    Oct 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks very much... it works great


  •  

    Posting Permissions

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