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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | hubert depesz lubaczewski | 2008-05-11 18:11:42 | Re: Difference in columns |
Previous Message | chester c young | 2008-05-11 17:54:38 | Re: Difference in columns |