Re: Difference in columns

--- Mag Gam <magawake@gmail.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