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.

Answer 2

UPDATE table SET total = (SELECT SUM(price) FROM test2 WHERE item = 'A' GROUP BY item) WHERE item = 'A';

Answer 3

Thanks for the answers, it got me thinking some more. I was able to do what I wanted in the end by using a temporary second table.

Here's what I did.

Create the test data:

CREATE TABLE test (id INT PRIMARY KEY,
                   price DECIMAL,
                   item CHAR(1),
                   total DECIMAL);

INSERT INTO test VALUES( 0, 1.0, 'A', NULL ), 
                       ( 1, 1.0, 'A', NULL ),
                       ( 2, 0.1, 'B', NULL ),
                       ( 3, 1.0, 'B', NULL ),
                       ( 4, 2.1, 'B', NULL ),
                       ( 5, 1.0, 'A', NULL ),
                       ( 6, 2.0, 'C', NULL );

Generate the temporary table:

SELECT SUM(price), item INTO temp_table FROM test GROUP BY item;

Update:

UPDATE test SET total = sum FROM temp_table WHERE temp_table.item=test.item;

Clean Up:

DROP TABLE temp_table;

Which yeilds:

select * FROM test ORDER BY id;

 id | price | item | total 
----+-------+------+-------
  0 |   1.0 | A    |   3.0
  1 |   1.0 | A    |   3.0
  2 |   0.1 | B    |   3.2
  3 |   1.0 | B    |   3.2
  4 |   2.1 | B    |   3.2
  5 |   1.0 | A    |   3.0
  6 |   2.0 | C    |   2.0
(7 rows)