Subtracting previous row minus current row in SQL
14,362
Solution 1
in My SQL
select a.Incoming,
coalesce(a.Incoming -
(select b.Incoming from orders b where b.id = a.id + 1), a.Incoming) as differance
from orders a
Solution 2
Use LAG
function in Oracle.
Try this query
SELECT DATE_IN,Incoming,
LAG(Incoming, 1, 0) OVER (ORDER BY Incoming) AS inc_previous,
LAG(Incoming, 1, 0) OVER (ORDER BY Incoming) - Incoming AS Diff
FROM orders
Solution 3
create table orders (date_in date, incoming_vol number);
insert into orders values (to_date('27.05.2015', 'DD.MM.YYYY'), 83);
insert into orders values (to_date('26.05.2015', 'DD.MM.YYYY'), 107);
insert into orders values (to_date('25.05.2015', 'DD.MM.YYYY'), 20);
insert into orders values (to_date('24.05.2015', 'DD.MM.YYYY'), 7);
insert into orders values (to_date('22.05.2015', 'DD.MM.YYYY'), 71);
The LAG function is used to access data from a previous row
SELECT DATE_IN,
incoming_vol as incoming,
LAG(incoming_vol, 1, incoming_vol) OVER (ORDER BY date_in) - incoming_vol AS incoming_diff
FROM orders
order by 1 desc
Another solution without analytical functions:
select o.date_in, o.incoming_vol, p.incoming_vol-o.incoming_vol
from orders p, orders o
where p.date_in = (select nvl(max(oo.date_in), o.date_in)
from orders oo where oo.date_in < o.date_in)
;
Author by
Gayathri
Updated on June 27, 2022Comments
-
Gayathri almost 2 years
I have a table
orders
.How do I subtract previous row minus current row for the column
Incoming
?