--- Mag Gam <[EMAIL PROTECTED]> wrote: > Hi All, > > I have a view that generates output similar to this. > > select * from foo.view; > > ts | size > -------------------+----- > 2002-03-16 | 11 > 2002-03-17 | 16 > 2002-03-18 | 18 > 2002-03-19 | 12 > > I am trying to find the difference between the size column. So the > desired > output would be > > ts | size| Diff > -------------------+-----+------ > 2002-03-16 | 11 | 0 > 2002-03-17 | 15 | 4 > 2002-03-18 | 18 | 3 > 2002-03-19 | 12 | -6 > > > I need the first column to be 0, since it will be 11-11. The second > colum is > 15-11. The third column is 18-15. The fourth column is 12-18. > > Any thoughts about this? >
select cur.ts, cur.size, cur.size - coalesce( (select size from view next where next.ts = cur.ts - '1 day'::interval), cur.size ) as diff from view cur; alternately: select cur.ts, cur.size, case when cur.ts = '2002-03-16' then 0 else cur.size - coalesce( (select size from view next where next.ts = cur.ts - '1 day'::interval), cur.size ) end as diff from view; ____________________________________________________________________________________ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql