SQLite - calculate percentage increase in values of a numeric column-CodePudding

I am interested in getting the % increase or decrease of the values in one column with respect to the previous value.

Can someone please advice how I should do this?

Thanks

This is what my table looks like

week |  sales  |

22   |  11     |
21   |  63     |
20   |  78     |
19   |  170    |
18   |  130    |

And this is what I would like

week |  sales  | growth

22   |  11     | -82.5
21   |  63     | -19.2
20   |  78     | -54.12
19   |  170    | 30.8
18   |  130    | NULL

CodePudding user response：

we need more details about what your trying to achieve here. Ex. week 22 and sales 11, how did you get the -82.5?

But basically, if you want to get the diff between 2 numbers by percentage. You can try this formula. Ex.

(@newSales - @oldSales) / ((@newSales   @oldSales) / 2) * 100

In your case:

SELECT (@sales - @week) / ((@sales   @week) / 2) * 100 AS growth

CodePudding user response：

Use LAG() window function to get the previous value of sales for each week:

SELECT *,
       ROUND(100.0 * (1.0 * sales / LAG(sales) OVER (ORDER BY week) - 1), 1) growth
FROM tablename
ORDER BY week DESC;

See the demo.