Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2009
    Posts
    58

    Unanswered: Need Help with this query

    Hi Guys,

    I have the below requirement.

    I have this table

    ID Mnth Value
    1 00 05
    1 01 10
    1 01 15
    1 01 22
    1 02 32
    1 02 38
    1 03 45
    1 03 52
    The value is a Aggregated column. Now i want to have a record for each month which should give me difference between the current month maximum and previous month maximum.

    So the output should be
    1 00 05
    1 01 17(22-05)
    1 02 16(38-22)
    1 03 14(52-38)
    Hope somebody can guide me..

    Cheers!!!!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    What about the ID column? Is that always the same for the same month?

  3. #3
    Join Date
    Jul 2009
    Posts
    58
    Quote Originally Posted by shammat View Post
    What about the ID column? Is that always the same for the same month?
    Yes, it will be same..

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Assuming you are on a current version of PostgreSQL (8.4 or later):
    Code:
    SELECT id, 
           mnth, 
           agg_value, 
           agg_value - lag(agg_value) over (order by mnth) as diff
    FROM (
       SELECT id,
               mnth,
              sum(value) as agg_value
       FROM your_table
       GROUP BY id, mnth
    ) T
    ORDER BY id, mnth

  5. #5
    Join Date
    Jul 2009
    Posts
    58
    Quote Originally Posted by shammat View Post
    Assuming you are on a current version of PostgreSQL (8.4 or later):
    Code:
    SELECT id, 
           mnth, 
           agg_value, 
           agg_value - lag(agg_value) over (order by mnth) as diff
    FROM (
       SELECT id,
               mnth,
              sum(value) as agg_value
       FROM your_table
       GROUP BY id, mnth
    ) T
    ORDER BY id, mnth
    will test it and let u knw incase of any issues...
    Thanks for your speedy help..
    cheers!!!!

  6. #6
    Join Date
    Jul 2009
    Posts
    58
    I am able to find one prob.. the Value column is already a aggregated column.. Then don we need to take the maximum value instead of summing it up??
    And also Lag function is not working in mine. so i think my database is a previous version.. i am also not sure about what version it is..
    Last edited by mac4rfree; 03-21-11 at 07:44.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Sorry I don't understand your last question (or I didn't understand what exactly is stored in the table and what exactly you need).

    In your example output you show only 1 row for month = 01 but there are three rows for that month available. So which difference would you like to see? The one between the first and the second value, or the second and the third, or the first and the third?

  8. #8
    Join Date
    Jul 2009
    Posts
    58
    no.. i think u got it wrong..
    I need the difference between the First month(01) maximum which is 22 and previous month(00) maximum which is 05.
    Hope i made myself clear..

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Then it should be max() instead of sum(), correct

  10. #10
    Join Date
    Jul 2009
    Posts
    58
    is there any alternative for lag function???? coz it not workin for me..

  11. #11
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Upgrade to a current version of Postgres

  12. #12
    Join Date
    Jul 2009
    Posts
    58
    i don think my client will upgrade,,, lol nice suggestion though..

Posting Permissions

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