CODEWITHSUNDEEP
sqlsql-server
Rajasekar Baskar
Rajasekar Baskar

Reputation: 53

Update the sum value in second row

I have table like followed by

User Col1 Col2 
ABC   35   75
ABC   500  75

and I need the select query for following output.

User Col1 Col2 Result
ABC   35   75    40
ABC   500  75    115

Check col1<col2 then Result Col2-Col1
else
Col2 + Result(Row1)

Ex:
35<75 then 40
else
75+40

I don't know how to start please help us for output..

Thanks in advance

What I have tried

Select User,Col1,Col2
,Case When Col1<Col2 then Col2-Col1 Else Col2 End as Result

Then i got the output like followed by

User Col1 Col2 Result
ABC  35   75    40
ABC  500  75    **75**

I need 115 instead of 75 in Result of second row

Upvotes: 0

Views: 99

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

First, you need a column that represents the ordering of the rows.

If I generalize the problem, then you need the groups defined by col1 < col2. You can define the groups by taking a cumulative sum of when col1 < col2.

The actual calculation is then a cumulative sum over the group:

select t.*,
       (case when col1 < col2 then col2 - col1
             else sum(case when col1 > col2 then col2 - col1 else col2 end) over (partition by user, grp order by ?)
        end) as result
from (select t.*,
             sum(case when col1 < col2 then 1 else 0 end) over (partition by user order by ?) as grp
      from t
     ) t            
from t;

The ? is for the column that specifies the ordering of the rows.

Upvotes: 0

Sahil Anand
Sahil Anand

Reputation: 139

You need to use the LAG function for this.here is the code...if you like the answer please vote.

Select *
   ,Case When Col1<Col2 then Col2-Col1 
    Else Col2+lag(col2-col1) over(partition by user order by col2 ) End as Result
 from review

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try below using lag() function

 Select User,Col1,Col2
   ,Case When Col1<Col2 then Col2-Col1 
    Else Col2+lag(col2-col1) over(partition by user order by col2 ) End as Result
 from tablename

Upvotes: 0

Deep patel
Deep patel

Reputation: 136

CREATE  TABLE #tmp
(
    UserName VARCHAR(10),
    col1 INT,
    col2 INT  
)

INSERT INTO #tmp (UserName, col1, col2)
          SELECT 'ABC',   35,   75
UNION ALL SELECT 'ABC',   500,  75

SELECT tmp.UserName
      ,tmp.col1
      ,tmp.col2
      ,CASE WHEN tmp.Result = 0 THEN tmp.col2 + LAG(tmp.Result) OVER (ORDER BY (SELECT NULL)) ELSE tmp.Result END  AS Result
FROM
(
Select UserName,Col1,Col2
,Case When Col1<Col2 then Col2-Col1 Else 0 End as Result
FROM #tmp
)tmp

Upvotes: 2

arahman
arahman

Reputation: 585

You could try something like this:

SELECT *
,Case When Col1 < Col2 then Col2-Col1 
  Else (Col2 + LAG(Col2-Col1 ,1) OVER(ORDER BY Col2)) --Order by
 End as Result
FROM TableName

By using LAG you can access the previous row of data. Then all I do is add the previous result to the Col2 value. 

Else (Col2 + LAG(Col2-Col1 ,1) OVER(ORDER BY Col2))

Important note below.

The only thing to note here is the ORDER BY Col2. At the moment that will work for just the two rows of data. But, if you have more you should add some sort of auto incrementing ID field to keep the order the same as how you enter the data. Then you would change it to ORDER BY ID.

Upvotes: 0

Related Questions