SQL query to get current and last year sales and their difference in oracle-CodePudding

I have the following table Sales:

year total_sale
2015 23000
2016 25000
2017 34000
2018 32000
2019 33000

This has to be the result

year current_total_sale previous_total_sale difference
2015 23000              NULL                NULL
2016 25000              23000               2000
2017 34000              25000               9000
2018 32000              34000               -2000
2019 33000              32000               1000

CodePudding user response：

Query using lag for the desired result -

select year_1, total_sale as current_total_sale, 
lag(total_sale) over (order by null) as previous_total_sale,
total_sale - lag(total_sale) over (order by null) difference
from sales;

DB fiddle here

CodePudding user response：

Self join, join the table to itself.

select a.year, a.total_sale as current_total_sale, b.total_sale as prev_total_sale,
b.total_sale - a.total_sale as difference
from have as a
left have as b
on a.year = b.year 1
order by 1;