CODEWITHSUNDEEP
sqlpostgresql
Luffydude
Luffydude

Reputation: 802

How to sum columns that have a field in common in Postgresql?

How to calculate total length for all rows that have a certain value?

Let's say there's the following table:

 id | unit_id | length | column to be filled with total length
 1  |   1     | 10
 2  |   1     |  4
 3  |   1     |  5
 4  |   2     |  3
 5  |   3     |  3
 6  |   3     |  6

In this case, how to update the table, making all the rows that have unit_id of 1 to have the sum of all the length of rows that have unit_id of 1 (10 + 4 + 5 = 19) then both rows that have a unit_id of 3 to have 9.

I've tried 

update test.routes 
set total_length = (select sum(length) from test.routes where unit_id = unit_id) where unit_id = unit_id

But what it does is that it just updates the entire table with the same value, how to update the correct sum for each unit_id?

Upvotes: 0

Views: 94

Answers (3)

Ratan Phayade
Ratan Phayade

Reputation: 86

This should do the work.

update 
    routes as s 
inner join (
    select unit_id, sum(length) as total_length from routes group by unit_id
) as g 
set 
    s.total_length = g.total_length 
where 
    s.unit_id = g.unit_id

Here we are creating a temporary table which has total length for the unit_id. By using the join between 2 tables we can do this bit efficiently then using a subquery

Upvotes: 0

Stephan Lechner
Stephan Lechner

Reputation: 35154

You need to qualify the reference to attribute unit_id. Otherwise, a constraint like where unit_id = unit_id is (apart from null-values) always true and will therefore sum up everything:

update test.routes r1 set total_length = (select sum(length) from test.routes r2 where r2.unit_id = r1.unit_id)

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51599

try CTE:

t=# with a as (select *, sum(length) over (partition by unit_id) from routes)
t-# update routes u set total_length = a.sum
t-# from a
t-# where a.id = u.id;
UPDATE 6
Time: 0.520 ms
t=# select * from routes ;
 id | unit_id | length | total_length
----+---------+--------+--------------
  1 |       1 |     10 |           19
  2 |       1 |      4 |           19
  3 |       1 |      5 |           19
  4 |       2 |      3 |            3
  5 |       3 |      3 |            3
  6 |       4 |      6 |            6
(6 rows)

Upvotes: 2

Related Questions