CODEWITHSUNDEEP
sqlsql-serverselect
persam
persam

Reputation: 13

sum the anothers rows value to the row in sql

The below table contained place name and distance KM, the row having KM between the previous row to place i.e The Place B have KM from A to B, c having the KM from B I want to find the total distance each place to each place in SQL 

Place   Distane in KM
A --->  0
B --->  80
C --->  90
D --->  100

I want to print the result as

Place   --->  To    --->  Distane in KM 
A --->  B   --->  80
A   --->  C --->  170
A   --->  D --->  270
B   --->  C --->  90
B   --->  D --->  190
C   --->  D --->  100

Upvotes: 0

Views: 69

Answers (2)

Mureinik
Mureinik

Reputation: 311338

You could self-join the table on rows where the destination is larger than the source, and use the window variant of sum:

SELECT   src.place + '->' + dst.place, 
         SUM(dst.distance) OVER (PARTITION BY src.place ORDER BY dst.place)
FROM     distances src
JOIN     distances dst ON src.place < dst.place
ORDER BY 1, 2

SQLFiddle

Upvotes: 2

Wei Lin
Wei Lin

Reputation: 3811

First you have to tag your DBMS like sql-server or oracle

It's demo for sql-server version and using row_number function with your order logic -> cross join self -> sum distance. 

with CTE as (
  select *,row_number() over (order by [Place]) rnk
  from T
)
select t1.Place + '->' + t2.Place as Place
  ,(select sum(Distane) from CTE t3 where t3.rnk > t1.rnk and t3.rnk <= t2.rnk  ) distance
from CTE t1,CTE t2
where t2.rnk > t1.rnk
order by Place

Result:

| Place | distance |
|-------|----------|
|  A->B |       80 |
|  A->C |      170 |
|  A->D |      270 |
|  B->C |       90 |
|  B->D |      190 |
|  C->D |      100 |

Test DDL:

CREATE TABLE T
    ([Place] varchar(1), [Distane] int)
;

INSERT INTO T
    ([Place], [Distane])
VALUES
    ('A', 0),
    ('B', 80),
    ('C', 90),
    ('D', 100)
;

Test DEMO

Upvotes: 0

Related Questions