PostgreSQL calculate difference between rows

postgresql, sql

Solution

This is what window functions are for:

select year, 
       month,
       fixes,
       fixes - lag(fixes) over (order by year, month) as increase,
from the_table;

For more details please see the manual: http://www.postgresql.org/docs/current/static/tutorial-window.html

Problem

I tried to calculate difference between rows in a field using a query: ``` Illustrations: input:year,month,fixes output:increase year | month | fixes | increase ------+-------+----------+----------- 2006 | 04 | 1 | 0 2006 | 05 | 4 | 3 2006 | 06 | 3 | -1 ``` Increase column as output by difference between adjacent rows in fixes.

Original source