CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Pavani Srujana
Pavani Srujana

Reputation: 81

Get sum of consecutive rows in one row

I have a table as:

Emp_id  Emp_Name    Department     Score
123     Raju            D1          300
124     Ravi            D2          400
125     Annie           D3          600
126     Ajay            D4          200
127     Amey            D5          500
128     Akil            D6          100

I need output as

So the output should be:

700
1000
800
700
600

I need the output through a SQL query and not a procedure or something else.

Upvotes: 2

Views: 7370

Answers (2)

8hZWKA
8hZWKA

Reputation: 455

It is very easy to do this with an analytic function that uses a sliding window from the current row to the next one (ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING):

SELECT Emp_id, Emp_Name, Department, Score, sum(Score)
  OVER (ORDER BY Emp_id ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) sum
FROM score;

When executing this query on your data the result is as follows:

123 Raju    D1  300 700
124 Ravi    D2  400 1000
125 Annie   D3  600 800
126 Ajay    D4  200 700
127 Amey    D5  500 600
128 Akil    D6  100 100

See Database SQL Language Reference, Analytic Functions for further details.

Upvotes: 5

Utsav
Utsav

Reputation: 8093

You need Lead function, which will show second row with first row and so on. Name it nxt_score. Then you can add score and nxt_score to get your output. You will also need nvl as for last row, the nxt_score will be null. So in that case, it will add the score with 0.

Remove other columns from select clause if you want.

SELECT
  t1.*,
  score + nvl(nxt_score, 0) AS cum_score
FROM 
    (SELECT
      t.*,
      LEAD(score, 1) OVER (ORDER BY emp_id) AS nxt_score
    FROM table1 t
    ) t1

Upvotes: 4

Related Questions