Re: [SQL] Difference in columns

On Sun, May 11, 2008 at 11:07 PM, 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?

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[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device