Re: Difference in columns

--- Mag Gam <magawake@[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@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql