sql - How to calculate a growing or decreasing percentage between rows usign group by -

let's suposse have table_1:

year item qty_sold 2013 1    3 2013 2    2 2013 3    5 2014 1    2 2014 2    3 

i'll perform this

select year , sum(qty_sold) quantity table_1 inner join table_2 on .... inner join table_n year = 2014 

the final result depends on filter year, there other tables involved.

but result need this:

year quantity  diff_percentage 2014 5         0.5 

because during previous year (2013) final quantity of items sold 10.

regards

you seem want this:

select year, sum(qty_sold) quantity,        lag(sum(qty_sold)) on (order year) prev_quantity,        (1 - quantity / lag(sum(qty_sold)) on (order year)) diff_percentage table group year; 

of course, returns info years. if want year 2014 , 2013 use conditional aggregation:

select year,        sum(case when year = 2014 qty_sold end) quantity_2014,        sum(case when year = 2013 qty_sold end) quantity_2013,        (1 - sum(case when year = 2014 qty_sold end)/             sum(case when year = 2013 qty_sold end)         ) diff_percentage table year in (2013, 2014); 

i'm sort of guessing on formula diff_percentage, think that's want.