Thread: Calculate difference between rows.
Hello there.
I have a fairly dumb question (I think), but I just can't seem to find my way out of it.
I have a simple table, wich reads:
postgres=# select * from car_fix order by dat ASC;
clientnr | receiptnr | description | dat
---------------+----------------+----------------------+------------
1 | 1 | Changed Tires | 2006-04-12
4 | 4 | Changed oil | 2006-05-12
4 | 3 | Regular Checkup | 2006-05-12
2 | 2 | Changed Tires | 2006-05-12
3 | 5 | Fixed Airbag | 2006-05-12
2 | 8 | Flat Tire | 2006-06-13
1 | 7 | Broken Axis | 2006-06-13
5 | 6 | Brake problem | 2006-06-13
(8 rows)
clientnr | receiptnr | description | dat
---------------+----------------+----------------------+------------
1 | 1 | Changed Tires | 2006-04-12
4 | 4 | Changed oil | 2006-05-12
4 | 3 | Regular Checkup | 2006-05-12
2 | 2 | Changed Tires | 2006-05-12
3 | 5 | Fixed Airbag | 2006-05-12
2 | 8 | Flat Tire | 2006-06-13
1 | 7 | Broken Axis | 2006-06-13
5 | 6 | Brake problem | 2006-06-13
(8 rows)
postgres=#
Which I then narrow down to services fixes per month, using:
postgres=# select to_char(dat, 'yyyy') as year, to_char(dat, 'mm') as month,
count(receiptnr) AS fixes from car_fix group by month,year order by month;
year | month | fixes
------+-------+-----------
2006 | 04 | 1
2006 | 05 | 4
2006 | 06 | 3
(3 rows)
count(receiptnr) AS fixes from car_fix group by month,year order by month;
year | month | fixes
------+-------+-----------
2006 | 04 | 1
2006 | 05 | 4
2006 | 06 | 3
(3 rows)
postgres=#
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
------+-------+----------+-----------
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?
gr
Freak
On Tue, Jun 13, 2006 at 16:02:36 +0200, Maurice Breeman <m.breeman@hccnet.nl> wrote: > > So far, so good. But now I wanna create an SQL select statement (of VIEW) which calculates the difference between the actualrow 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. Cansomebody 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.