How to set new column equal to subquery PostGreSQL -

full disclosure: i've seen 1 variation of question mysql, , postgresql answer didn't satisfy me.

i have 2 tables: reviews & businesses. in reviews table, 3 relevant columns purpose of question 'business_id', 'date' (yyyy-mm-dd), , stars (1-5), , primary key (review_id). in businesses table, relevant columns 'business_id', 'year', , 'month'.' 'year' , 'month' columns there because there column in business table called 'review_count', represents number of reviews business received on each month of each year. because of this, composite primary key of table (business_id, year, month).

essentially, i trying create column in business table average rating (represented stars) business received on each month of each year open.

the following query gives me exact result want:

select round(cast(avg(stars) numeric), 2)                                     reviews_for_trending_businesses                                     business_id in (select distinct(business_id)                                                                           trending_businesses_v2)                                     group business_id, extract("year" reviews_for_trending_businesses.date), extract('month' reviews_for_trending_businesses.date); 

this code returns column , correct values want insert business table.

however, when try update table, error saying more 1 row returned subquery used expression. code i'm trying update with:

update trending_businesses_v2 set avg_monthly_rating = (select round(cast(avg(stars) numeric), 2)                                         reviews_for_trending_businesses                                         business_id in (select distinct(business_id)                                                                               trending_businesses_v2)                                         group business_id, extract("year" reviews_for_trending_businesses.date), extract('month' reviews_for_trending_businesses.date); 

i've tried number of other solutions well, including using joins, keep getting similar error.

update: still no answer getting closer: still can't quite figure out i'm going wrong here. don't understand why have groupby 'rtb.date' here if i'm extracting values (returned error if didn't).

update trending_businesses_v2 tb     set avg_monthly_rating = t.val     (select business_id, extract("year" rtb.date) year, extract('month' rtb.date) month, round(cast(avg(stars) numeric), 2) val           reviews_for_trending_businesses rtb           business_id in (select distinct(business_id)                                  trending_businesses_v2                                )           group business_id, year, month, rtb.date          ) t     t.business_id = tb.business_id ,           t.year = tb.year , t.month = tb.month; 

you need match rows, presumably using business id , date. this:

update trending_businesses_v2 tb     set avg_monthly_rating = t.val     (select business_id, date_trunc('month', rtb.date) yyyymm, round(cast(avg(stars) numeric), 2) val           reviews_for_trending_businesses rtb           business_id in (select distinct(business_id)                                  trending_businesses_v2                                )           group business_id, date_trunc('month', rtb.date)          ) t     t.business_id = tb.business_id ,           t.yyyymm = tb.?;