From: | chester c young <chestercyoung(at)yahoo(dot)com> |
---|---|
To: | Mag Gam <magawake(at)gmail(dot)com> |
Cc: | sql pgsql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Difference in columns |
Date: | 2008-05-11 17:54:38 |
Message-ID: | 48295.53084.qm@web54307.mail.re2.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
--- 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
From | Date | Subject | |
---|---|---|---|
Next Message | Gurjeet Singh | 2008-05-11 17:55:06 | Re: Difference in columns |
Previous Message | Mag Gam | 2008-05-11 17:37:52 | Difference in columns |