# Calculating the percentage change from the previous row

I just entered the world of window functions. I have a similar problem to solve as in this question.

I have this table:

The last column `(%Grown)` is given by:

``````-(1 - price of row 2 / price of row 1) * 100
``````

All the information I have found so far was related to sums, avg for all rows. I don't know how to bound two rows so I can calculate the percentage.

How do I translate this into SQL?

• What column should be used for the ordering of rows? Aug 27, 2015 at 13:26

The best way I think would be to use the `LAG()` or `LEAD()` functions:

``````SELECT *,
- 100.0 * (1 - LEAD(Price) OVER (ORDER BY t.Id) / Price) AS Grown
FROM table_name AS t
ORDER BY t.Id ;
``````

With `LEAD(Price) OVER (ORDER BY t.Id)` you have access to the next `Price` when the rows are ordered by `Id`. It's not clear what the order should be. Based on the data in the question, you may want `(ORDER BY Price DESC)`.

• Thank you for your answer. Actually, the table is a query and I'm trying to calculate the price variation from one purchase date to another. (say you buy some bricks in 01.01.2015 with 5\$, on 05.01.2015 with 10\$ and on 10.01.2015 with 10\$) I think it may work with a function as well, but I'm so stuck...
– ralu
Aug 28, 2015 at 12:45
• It doesn't matter if it's a base table or a query result. Just replace `table_name` with your query `(SELECT ....)` and play with the `(ORDER BY ...) ` inside the `OVER`. Aug 28, 2015 at 13:00

You can use Common Table expression,

``````WITH Data as (select  ROW_NUMBER() OVER (ORDER BY applicable_date) as RowId, date, qty, price, [value] from tbl
)
SELECT
CurrentRow.date, CurrentRow.qty, CurrentRow.price, CurrentRow.[value], 'Your calculation here for Growth'
FROM Data as CurrentRow
LEFT OUTER JOIN Data as NextRow ON NextRow.RowId = CurrentRow.RowId + 1
WHERE NextSlice.RowId IS NOT NULL
``````

I Hope it helps.

• `[value]` is an invalid identifier (`[` or `]` or not allowed in an SQL identifier)
– user1822
Aug 27, 2015 at 12:15
• in postgres you can use reserved words with " "
– ralu
Aug 27, 2015 at 12:46
• Thank you, Mouliin. However, I am lost. What's NextSlice?
– ralu
Aug 27, 2015 at 12:49
• Sorry, It should be NextRow and CurrentRow. I only used [value] because it was in the original table structure. Aug 27, 2015 at 13:20
• There was a `Value` in the question, not a `[Value]`
– user1822
Aug 27, 2015 at 13:32