How to calculate difference between two rows in SQL?

Question

My tables are:

Bowlers: BName - Handed - Phone

Performances: BName - TName - Score

Tournaments: TName - TDate

I am trying to figure out how to calculate the difference in score for a bowler named Fred. My code so far is:

select tname, tdate, score
from tournaments
natural inner join 
performances
where bname = 'Fred'
order by tdate;

This gives me a table like this:

 TName                    TDate                Score
 ------------------------ ------------ -------------
 Tournament 1             1/1/2014               250
 Tournament 2             1/8/2014               245
 Tournament 3             2/10/2014              215

Now I just need to add a fourth column that calculates the difference in his score from the previous tournament. So the finished table would look like this:

 TName                    TDate                Score     Score_Dif
 ------------------------ ------------ ------------- -------------
 Tournament 1             1/1/2014               250             0
 Tournament 2             1/8/2014               245            -5
 Tournament 3             2/10/2014              215           -30

Any help is greatly appreciated!

Possible duplicate of [How to get difference between two rows for a column field?](http://stackoverflow.com/q/634568/456814). — , Oct 05 '15 at 11:11

Gordon Linoff · Accepted Answer · 2014-03-11T18:19:08.843

1

SQLPlus is usually attached to Oracle, which supports the lag() function. So, you can do what you want pretty easily:

select tname, tdate, score,
       coalesce(score - lag(score) over (order by tdate), 0) as diff
from tournaments natural inner join 
     performances
where bname = 'Fred'
order by tdate;

edited Mar 11 '14 at 18:19

answered Mar 11 '14 at 18:11

Gordon Linoff

1,242,037
58
646
786

Ah nevermind, just had to change it to "tdate" thank you very much! I couldn't remember the lag function, that helped out a lot! – user3407345 Mar 11 '14 at 18:20

score 0 · Answer 2 · edited May 23 '17 at 12:15

Assuming that you are working with MySQL:

You can use a temporary variable to hold the previous value, and then you can calculate the difference. Take a look to this question and its accepted answer.

For your specific purpose, I would create a temp table to hold the data, and then I would query that temp table and calculate the differences:

drop table if exists temp_data;
create temporary table temp_data
    select 
        tname, tdate, score
    from 
        tournaments
        natural inner join performances
    where 
        bname = 'Fred';
alter table temp_data
    add index idx_tname(tname),
    add index idx_tdate(tdate);
select tname, tdate, score, score_dif
from
    (
        select 
            tname, tdate, 
            @prevScore as previousScore, 
            a.score - @prevScore as score_dif, 
            @prevScore := a.score as score
        from
            (
                select @prevScore := 0
            ) as init,
            temp_data as a
        order by a.tdate
    ) as calcQuery;

Hope this helps.

Ok, you can drop this answer (you are not using MySQL). Next time please tag your question correctly — Barranka, Mar 11 '14 at 18:16
I apologize, I tried to change it as soon as I posted but I messed it up. — user3407345, Mar 11 '14 at 18:18

How to calculate difference between two rows in SQL?

2 Answers2