title: SQL select for average from another table
body: <p>I spent a lot of time building this select, but Im not able to solve it. I have 2 tables. First table is called car and has PK (primary key) id_car and another columns name and so on. Second table is called rating and has colums id_rating (PK), id_car (FK) and rating_value (integer). As you suspect, one car can have more than one ranting. I want to select all cars and I want to know average rating to each car. Finally, I want to order the result by this average desc. I was trying things like this:</p>
  
  <pre><code>    SELECT id_car, name, average
      FROM car C, rating R 
      WHERE C.id_car = R.id_car 
      ORDER BY (average) (
          SELECT AVG(rating_value) AS average 
          FROM rating R
          WHERE C.id_car = R.id_car)
  </code></pre>
  
  <p>but it doesn't work. </p>