Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010
    Posts
    10

    Unanswered: how to do the minus the current row from the previous row

    hi all,
    I have a table 'class' having fields (class,year, quarter,count).
    values of the table like

    class year quarter count
    Nokia 2006 Q1 100
    Nokia 2006 Q2 200
    Nokia 2006 Q3 50
    Nokia 2006 Q4 150

    I want to find

    class year quarter count
    Nokia 2006 Q1 100(100-0)
    Nokia 2006 Q2 100(200-100)
    Nokia 2006 Q3 -150(50-200)
    Nokia 2006 Q4 100(150-50)

    Means I want to subtract the current row value of 'count' from the previous row value of 'count'.

    Please help me regarding this.

    Thanks.

  2. #2
    Join Date
    Aug 2010
    Posts
    14
    make quarter column as your primary key so that it will be unique and keeps on inreasing....like i am gonna create a table where "id_no" is my primary key jus like make quarter ..ur primary key.


    id_no roll_no
    ------- ----------
    1 10
    2 20
    3 30

    then syntax wud be


    select a.id_no, a.roll_no, a.roll_no - b.roll_no as result
    from
    temp a inner join temp b ON a.id_no+1 = b.id



    here temp is the name of the table and result is an alias for retrieved id_no..

    thanks.

  3. #3
    Join Date
    Nov 2006
    Posts
    82
    If you use postgres >=8.4 you can achieve it using 'window functions' and lag() function.
    See here
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Window Functions
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Window Functions

    It would be something like that
    Code:
    select class, year, quarter, count - lag(count) over (partition by quarter order by quarter) as "count" from class;

  4. #4
    Join Date
    Jul 2010
    Posts
    10
    thanks buddy. It is a great help from U.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •