SQL - Updating a table, such that a column is summed given another column's key

Question

Given a table:

| id | price | item | total |
| 0  |  1.0  |  A   |       |
| 1  |  1.0  |  A   |       |
| 2  |  0.1  |  B   |       |
| 3  |  1.0  |  B   |       |
| 4  |  2.1  |  B   |       |
| 5  |  1.0  |  A   |       |
| 6  |  2.0  |  C   |       |

is there an SQL statement that will lead to this ?.

| id | price | item | total |
| 0  |  1.0  |  A   |  3.0  |
| 1  |  1.0  |  A   |  3.0  |
| 2  |  0.1  |  B   |  3.1  |
| 3  |  1.0  |  B   |  3.1  |
| 4  |  2.1  |  B   |  3.1  |
| 5  |  1.0  |  A   |  3.0  |
| 6  |  2.0  |  C   |  2.0  |

Where, each item is has all the prices sum'd. I can do a SELECT ...

SELECT SUM(price), item FROM table GROUP BY item;

but I can't figure out how to do an UPDATE. p.s. I'm using Postgres.

Thanks

Answer 1

Create an AFTER trigger that does the update of the aggregated rows.