postgresqlaggregate-functionswindow-functions

Is it possible to do mathematical operations on values in the same column but different rows?

Say I have this table,

 year |     name      | score
------+---------------+----------
 2017 | BRAD          |   5
 2017 | BOB           |   5
 2016 | JON           |   6
 2016 | GUYTA         |   2
 2015 | PAC           |   2
 2015 | ZAC           |   0

How would I go about averaging the scores by year and then getting the difference between years?

 year |  increase
------+-----------
 2017 | 1
 2016 | 3
Solution

  • You should use a window function, lead() in this case:

    select year, avg, (avg - lead(avg) over w)::int as increase
from (
    select year, avg(score)::int
    from my_table
    group by 1
    ) s
window w as (order by year desc);

 year | avg | increase 
------+-----+----------
 2017 |   5 |        1
 2016 |   4 |        3
 2015 |   1 |         
(3 rows)