CODEWITHSUNDEEP
sql-server-2012
CompChemist
CompChemist

Reputation: 923

SQL SERVER 2012: Subtract elements in a single column

Is there an easy way in SQL Server 2012 to subtract the n+1 - n value in a column?

I.e. suppose I have data like this.

    name value
    matt 25
    val  100
    hal  75
    brit 0

I want to get a result like this

    name value subtract
    matt 25    25
    val  100   75
    hal  75   -25
    brit 0    -75

Upvotes: 0

Views: 34

Answers (1)

Magnus Eklund
Magnus Eklund

Reputation: 415

Sure but you need to specify the order of your query. Use LAG with a window function.

SELECT name, value, value - LAG(value, 1, 0) OVER (ORDER BY newid()) FROM your table

The order by newid() you need to replace with your desired order.

See fiddle example: http://sqlfiddle.com/#!6/1598b/6

Upvotes: 1

Related Questions