select "masters".*, (select avg (rating) 
from "reviews" where "master_id" = "masters"."id") as rating 
from "masters" where rating > 3

[42703] ERROR: column "rating" does not exist


You can't use a column alias on the same level where you defined it. You need to wrap the query:

select *
from (
  select masters.*, (select avg(rating) 
                     from reviews 
                     where master_id = masters.id) as rating 
  from masters 
) t
where rating > 3;

A more efficient solution is however, to do the aggregation once for all rows and then join to the result:

select m.*, r.rating
from masters m
  join (
     select master_id, avg(rating)  as rating
     from reviews 
     group by master_id
     having avg(rating) > 3
  ) r on r.master_id = m.id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.