SQL select for average from another table

Question

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:

    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)

but it doesn't work.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

For SQL Server; Also I suggest you to use JOIN instead of WHERE table1, table2..

SELECT C.id_car, name, AVG(rating_value) AS average
FROM car C JOIN rating R 
      ON C.id_car = R.id_car
GROUP By C.id_car, name
ORDER BY average DESC

SQL select for average from another table

Sign up