CODEWITHSUNDEEP
mysql
Lin Ma
Lin Ma

Reputation: 10139

calculate the differences between two rows in SQL

I have a SQL table, one row is the revenue in the specific day, and I want to add a new column in the table, the value is the incremental (could be positive or negative) revenue between a specific day and the previous day, and wondering how to implement by SQL?

Here is an example,

original table,

...
Day1 100
Day2 200
Day3 150
...

new table (add incremental column at the end, and for first column, could assign zero),

Day1 100 0
Day2 200 100
Day3 150 -50

I am using MySQL/MySQL Workbench.

thanks in advance, Lin

Upvotes: 3

Views: 16085

Answers (3)

Nir-Z
Nir-Z

Reputation: 869

SELECT a.day, a.revenue , a.revenue-COALESCE(b.revenue,0) as previous_day_rev 
FROM DailyRevenue a 
LEFT JOIN DailyRevenue b on a.day=b.day-1

the query assume that each day has one record in the table. If there could be more than 1 row for each day you need to create a view that sums up all days grouping by day.

Upvotes: 4

Jon Marnock
Jon Marnock

Reputation: 3225

If you're okay with re-ordering the columns slightly, something like this is pretty simple to understand:

SET @prev := 0;
SELECT day, revenue - @prev AS diff, @prev := revenue AS revenue
FROM revenue ORDER BY day ASC;

The trick is that we calculate the difference to the previous first, then set the previous to the current and display it as the current in one step.

Note, this depends on the order being correct since the calculations are done during the returning of the rows, so you need to make sure you have an ORDER BY clause that returns the days in the correct order.

Upvotes: 3

Praveen
Praveen

Reputation: 9335

Try;

select 
    t.date_col, t.val_col,
    case when t1.val_col is null then 0
    else t.val_col - t1.val_col end diff
from (
    select t.* , @r := @r + 1 lev
    from tbl t,
    (select @r := 0) r
    order by t.date_col
) t
left join (
    select t.* , @r1 := @r1 + 1 lev
    from tbl t,
    (select @r1 := 1) r
    order by t.date_col 
) t1
on t.lev = t1.lev

This will calculate value diff even if there is a missing date

Upvotes: 1

Related Questions