SQL - update the table so that the columns are summed with another column key

Question

For the 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 product has all the prices, the amount. I can do SELECT ...

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

but i cant figure out how to do UPDATE. postscript I am using Postgres.

thank

Answer 1

AFTER, .

Answer 2

, . , , , .

.

:

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 );

:

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;

:

DROP TABLE temp_table;

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)

Answer 3

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