In January, 2018, 100
In February, 2018, 200
In January, 2019, 300
In February, 2019, 400
I want to achieve:
2018 total 2019 total rose
300 700 400
I know that you can use the
Select sum (a. data), sum (b. data), sum (b. data - a. data) from table as a, table as b
Where a. Time=2018 and b.=2019
This kind of way to solve the
But this need the connection, the large amount of data will be very card
Excuse me, can you do direct check 2018, 2019, check the final reduction
Sideways is, in other words, until, with is what?
Thank you very much
CodePudding user response:
With xx as(select 1 as amount, 201801 as the year
The from dual
Union all
Select 2 as amount, 201802 as the year
The from dual
Union all
Select 11 as amount, 201901 as the year
The from dual
Union all
Select 2 as amount, 201902 as the year
The from dual)
Select sum (amount) as tot_amount,
Lag (the sum of (amount)) over (order by substr (year, 1, 4)) as last_tot_amount,
The sum (amount) - NVL (lag (the sum of (amount)) over (order by substr (year, 1, 4)), sum (amount)) as minus_amount,
Substr (year, 1, 4) as year
The from xx
Group by substr (year, 1, 4)