I have 2 tables: dish and rating. Dish:

Rating:

In dish: id is primary. In rating: dish is foreign key to dish.id table and user is foreign key also but don't worry about user.

So i need to count average rating of each dish.

It will look like this:

i dunno how to write such a hard SQL request need help :3

CodePudding user response：

use left join and group by clause.

select 
    d.id as dishid,
    avg(r.rate) as "aver. rate"
    dish d 
    left join Rating r on d.id =r.dishid
    group by d.id

CodePudding user response：

That's basically a LEFT JOIN with GROUP BY. Very important here is to use COALESCE.

Why that?

If there is no rating for a dish, you will get 0 as result instead of NULL (which is intended according to your description).

So your query will be like this:

SELECT 
  d.id AS dishId, AVG(COALESCE(r.rate,0)) AS "aver. rate"
FROM dish d 
  LEFT JOIN rating r 
  ON d.id = r.dish
GROUP BY d.id;