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

Question

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

Erwin Brandstetter · Accepted Answer · 2022-10-24 02:58:55Z

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:

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related