CODEWITHSUNDEEP
sqlsql-servert-sql
eddie_cat
eddie_cat

Reputation: 2583

How can I select the difference between two rows?

Here's an example of what I'm looking for:

I have data that comes in as a lifetime total in gallons. I want to be able to display the data as a running total over the time period I am selecting for rather than as a lifetime total. For example:

timestamp   lifetimeTotal        runningTotal
1:30            3000                 0
1:31            3001                 1
1:32            3005                 5
1:33            3010                 10

I'm not sure how to go about doing this. I was looking at examples like this one using over but it's not quite what I'm looking for: I don't want to add the rows together every time, rather I want to add the difference between the two rows. Right now I am simply selecting the lifetime totals and displaying that.

Any ideas? I will add code if necessary but there's not much to show besides my select statement; I am having trouble thinking of a way to do this conceptually.

Upvotes: 1

Views: 69

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

This can be easily done using window functions:

SELECT [timestamp], lifetimeTotal, 
       COALESCE(SUM(diff) OVER (ORDER BY [timestamp]), 0) AS runningTotal
FROM (
SELECT [timestamp], 
       lifetimeTotal,
       lifetimeTotal - LAG(lifetimeTotal) OVER (ORDER BY [timestamp]) AS diff
FROM mytable ) t

The above query uses LAG to calculate the difference between current and previous row. SUM OVER is then used in an outer query to calculate the running total of the difference.

Demo here

Upvotes: 2

JamieD77
JamieD77

Reputation: 13949

This should give difference between the current lifetimeTotal and the min lifetimeTotal 

SELECT timestamp, 
       lifetimeTotal,
       lifetimeTotal - MIN(lifetimeTotal) OVER () as runningTotal
FROM   Table

Upvotes: 2

Related Questions