I have a table with a structure like this:
op | name | amount | total | 1 | a | 40 | 2 | 2 | a | 80 | 4 | 5 | b | 30 | 5 | 6 | b | 50 | 7 | 1 | c | 20 | 1 | 3 | c | 30 | 4 |
I want the amount and total columns to be added according to the name column (I need the op column to keep appearing). For this example, my select would return this result:
op | name | amount | total | 1 | a | 120 | 6 | 2 | a | 120 | 6 | 5 | b | 80 | 12 | 6 | b | 80 | 12 | 1 | c | 50 | 5 | 3 | c | 50 | 5 |
Can I do this using PostgreSQL?
Advertisement
Answer
Create schema:
create table tab ( op integer, name varchar, amount integer, total integer ); insert into tab values (1,'a',40,2), (2,'a',80,4), (5,'b',30,5), (6,'b',50,7), (1,'c',20,1), (3,'c',30,4);
SELECTstatement:
select t1.op, t1.name, t2.amount, t2.total
from tab t1
inner join (select name,
sum(amount) as amount,
sum(total) as total
from tab
group by name) t2
on t1.name = t2.name
Result:
| op | name | amount | total |
|---|---|---|---|
| 1 | a | 120 | 6 |
| 2 | a | 120 | 6 |
| 5 | b | 80 | 12 |
| 6 | b | 80 | 12 |
| 1 | c | 50 | 5 |
| 3 | c | 50 | 5 |
Test it here: db<>fiddle