Re: Calculate difference between rows.

On Tue, Jun 13, 2006 at 16:02:36 +0200,
Maurice Breeman <m(dot)breeman(at)hccnet(dot)nl> wrote:
>
> So far, so good. But now I wanna create an SQL select statement (of VIEW) which calculates the difference between the actual row and the previous row
>
> The output should be something like this:
>
> year | month | fixes | increase
> ------+-------+----------+-----------
> 2006 | 04 | 1 | 0
> 2006 | 05 | 4 | 3
> 2006 | 06 | 3 | -1
>
> (3 rows)
>
>
> Or even in percentages, but, that's no big deal, once I find out the way to calculate the difference between rows. Can somebody give me a hand?

It is probably best to do this in the client application rather than SQL.
It is possible to do it in SQL but it won't be all that efficient. The idea
is to do a self join between the month of interest and the previous month.
You will probably want to use a left (or right join) to make sure there is
a row for every month with fixes, even if there are no fixes the month before.
Also note that sum() returns NULL in the case where there are no records.
(This is brain damaged behavior, but somehow it got put into the SQL spec
with that definition.)
You need to also thnk about what you want for cases when there are no records
for the previous month. In your example it seems odd that the first month with
fixes isn't seen as an increase over the previous month.