Re: Difference in columns

--- Mag Gam <magawake(at)gmail(dot)com> 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