How can I select the difference between two rows?

Question

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.

yeah but what it mean by running total nothing has been adding for that simply showing 0,1,5 etc — mohan111, Jul 13 '15 at 15:35
@mohan111 it's the difference between rows in the lifetimeTotal column, if you can think of a better name for explanation purposes feel free to edit — eddie_cat, Jul 13 '15 at 15:36
Doesn't look like a running total, rather the difference between `lifetimeTotal` and the `lifetimeTotal` of the first row... — TT., Jul 13 '15 at 15:37
select lifetimeTotal, lifetimeTotal - (select min(lifetimeTotal) from .... where ...) — paparazzo, Jul 13 '15 at 15:37
possible duplicate of [Calculate a Running Total in SqlServer](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver) — Tab Alleman, Jul 13 '15 at 15:43
@TabAlleman this is not a duplicate of that question, i don't want to add the rows together. Did you even read the question — eddie_cat, Jul 13 '15 at 15:45

Giorgos Betsos · Answer 1 · 2015-07-13T15:52:11.980

2

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

edited Jul 13 '15 at 15:52

answered Jul 13 '15 at 15:47

Giorgos Betsos

71,379
9
63
98

This is exactly what I am looking for. It seems to work for the first few records but then it goes all weird... after the tenth row (with the runningTotal correct up to then) it jumps from 8 to -105087 and it just stays in the huge negatives after that... the lifetimeTotal is increasing normally, not sure why that would happen. Only difference between our code is that I am casting to decimal because for whatever reason lifetimeTotal is stored as a varchar. – eddie_cat Jul 13 '15 at 15:53

score 2 · Accepted Answer · answered Jul 13 '15 at 15:51

2

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

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

answered Jul 13 '15 at 15:51

JamieD77

13,796
1
17
27

1

I can see this potentially being an issue if for some reason the totals were to *decrease*, rather than *increase*. – Siyual Jul 13 '15 at 15:53
For what it's worth, that's not an issue for me in particular :) values will always go up. I'm going to try this out, thanks – eddie_cat Jul 13 '15 at 16:01

How can I select the difference between two rows?

2 Answers2