update average/count from another table

I’ve been provided the below schema for this problem and I’m trying to do two things:

1. Update the ACCOUNT table’s average_eval row with the average of the evaluation row from the POST_EVAL table per account_id.
2. Update the ACCOUNT table with a count of the number of posts per account_id, with default value 0 if the account_id has no post_id associated to it.

Here’s the kicker : I MUST use the UPDATE statement and I’m not allowed to use triggers for these specific problems.

I’ve tried WITH clauses and GROUP BY but haven’t gotten anywhere. Using postresql’s pgadmin for reference.

Any help setting up these queries?

Answer

The first question can be done using something like this:

update account a
  set average_eval = t.avg_eval
from (
  select account_id, avg(evaluation) as avg_eval
  from post_eval 
  group by account_id
) t
where t.account_id = a.account_id

The second question needs a co-related sub-query as there is no way to express an outer join in an UPDATE statement like the above:

update account a
  set num_posts = (select count(*)
                   from post p
                   where p.account_id = a.account_id);    

The count() will return zero (0) if there are no posts for that account. If a join was used (as in the first statement), the rows would not be updated at all, as the “join” condition wouldn’t match.

I have not tested either of those statements, so they can contain typos (or even logical errors).

---

Unrelated, but: I understand that this is some kind of assignment, so you have no choice. But as RiggsFolly has mentioned: in general you should avoid storing information in a relational database that can be derived from existing data. Both values can easily be calculated in a view and then will always be up-to-date.