row operation in same table and same coloumns

Question

Hi! i have a little problem to discuss i hope it will easy for you. suppose i have table A with 2 columns as

item          price
milk           25
milk           50
milk           100
Butter         25
Butter         100
Butter         200

now i want to display a table B derived from table A as

item          price growth rate
milk           0
milk           100
milk           100
Butter        100
Butter         200
Butter         100

formula for growth rate for row1 is

((row[1]-row[0])/row[0])*100
eg for 1st row ((50-25)/25)*100

can you suggest a SQl Query for it

Answer 1

The main trick for you is to write SQL that will get two consecutive rows in a single row.

Depending on the server there are a few ways to do that

For example you could join each row from one query to subquery that will get the first row that has a value lower then the value in current row.

To get the results you want you will also need to use a function that will convert nulls to 0.

Note: Your example in results row 4 lists growth rate 100; this is inconsistent with the example in the first row.