Skip site navigation (1) Skip section navigation (2)

Re: Difference in columns

From: "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com>
To: "Mag Gam" <magawake(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Difference in columns
Date: 2008-05-11 17:55:06
Message-ID: 65937bea0805111055q4945bb1bif677c2f1d4082d80@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Sun, May 11, 2008 at 11:07 PM, 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?


Try this:

select   ts,
            size,
            t1.size - (select t2.size
                            from foo.view as t2
                            where t2.ts < t1.ts
                            order by ts desc
                            limit 1) as diff
from foo.view as t1
order by ts asc;

HTH,

-- 
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

In response to

pgsql-sql by date

Next:From: hubert depesz lubaczewskiDate: 2008-05-11 18:11:42
Subject: Re: Difference in columns
Previous:From: chester c youngDate: 2008-05-11 17:54:38
Subject: Re: Difference in columns

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group