Sum of columns with same name in PostgreSQL

user_15663464 0
Viewed 88

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?

sum postgresql sql
2 Answers

You can use window function SUM() OVER for that

CREATE TABLE tab1 (
  "op" INTEGER,
  "name" VARCHAR(1),
  "amount" INTEGER,
  "total" INTEGER
);

INSERT INTO tab1
  ("op", "name", "amount", "total")
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 
"op"
, "name", SUM("amount") OVER (PARTITION BY "name") as "amount"
, SUM("total") OVER (PARTITION BY "name") as "total"
FROM tab1

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

db<>fiddle here

user_51935360

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

user_140157370
Related