CODEWITHSUNDEEP
sql-serverlag
froggy
froggy

Reputation: 51

How to add value from previous row with lag in a single sql select statement?

I have a sql table with column A and B, and a third column C = A - B.

For example:

Row 1: A = 10, B = 8, C = 2
Row 2: A = 7, B = 8, C = -1
Row 3: A = 0, B = 8, C = -8

I would like to sum up the values of the third column C in a fourth column D. The column D should be value of D of the last row plus the value of C of the current row. Example:

Row 1: A = 10, B = 8, C = 2, D = 2
Row 2: A = 7, B = 8, C = -1, D = 1
Row 3: A = 0, B = 8, C = -8, D = -7

I tried to use the following SQL:

SELECT myTable2.*, D = LAG(D) + C OVER (ORDER BY Id)
FROM 
(
    SELECT myTable.*, C = myTable.A - myTable.B
    FROM 
    (
        SELECT Id = 1, A = 10, B = 8
        UNION
        SELECT Id = 2, A = 7, B = 8
        UNION
        SELECT Id = 3, A = 0, B = 8
    ) myTable
) myTable2
ORDER BY myTable2.Id ASC

I can get LAG(C), but not LAG(D). Any ideas how I could achieve this?

Thanks.

Upvotes: 0

Views: 1085

Answers (3)

TheGameiswar
TheGameiswar

Reputation: 28900

create table 
#test
(
a int,
b int,
c int
)

insert into #test
select 1,2,3
union all
select 3,4,5

with cte
as
(
select *,0 as 'd', row_number() over (order by a,b,c) as rn
from #test
)
select *,(select sum(c+d) from cte c1 where c1.rn<=c2.rn) 'D'
from cte c2

Upvotes: 0

Liesel
Liesel

Reputation: 2979

Since there's no id on your data, I've sorted by A descending.

SELECT A, B, A-B C, SUM(A-B) OVER (ORDER BY A DESC) D FROM (VALUES (10,8),(7,8),(0,8)) A(A,B)

A           B           C           D
----------- ----------- ----------- -----------
10          8           2           2
7           8           -1          1
0           8           -8          -7

(3 row(s) affected)

Upvotes: 1

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

You can try this:

SELECT myTable2.*,  D = ISNULL(SUM(C) OVER (ORDER BY Id), 0)
FROM 
(
    SELECT myTable.*, C = myTable.A - myTable.B
    FROM 
    (
        SELECT Id = 1, A = 10, B = 8
        UNION
        SELECT Id = 2, A = 7, B = 8
        UNION
        SELECT Id = 3, A = 0, B = 8
    ) myTable
) myTable2

Upvotes: 0

Related Questions