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);
SELECT
statement:
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