Search code examples
sqldatabasepostgresqlsql-updateaggregate

Sum values by id, and update another table with the results

I have two tables:

  • Users with ID and Amount_due columns
  • Bills with ID, billed_cents, User_Id

I need to sum all the billed_cents in the bills table by ID and update by ID the user's amount_due.

Here is what I am trying so far with no luck

UPDATE users
SET amount_due = (SELECT SUM(billed_cents) FROM bills INNER JOIN users ON bills.user_id = users.id WHERE someConditions)
FROM bills
WHERE users.id = bills.user_id
Solution

  • The most efficient query depends on undisclosed details. This works in any case:

    UPDATE users u
SET    amount_due = b.sum_cents
FROM   (
   SELECT user_id, sum(billed_cents) AS sum_cents
   FROM   bills
   WHERE  <some conditions>  -- maybe can go here? (better if possible)
   GROUP  BY 1
   ) b
WHERE  u.id = b.user_id 
-- AND <some conditions>     -- may have to go here?

    This form does not involve the table users (a second time) in the subquery.

    For selective filters on users, it may be more efficient to add the join back and filter before aggregating.

    See: