Need Help with this query

mac4rfree · #1 (**permalink**) 03-21-11, 05:28

Hi Guys,

I have the below requirement.

I have this table

Quote:

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

Quote:

1 00 05
1 01 17(22-05)
1 02 16(38-22)
1 03 14(52-38)

Hope somebody can guide me..

Cheers!!!!

shammat · #2 (**permalink**) 03-21-11, 05:44

What about the ID column? Is that always the same for the same month?

mac4rfree · #3 (**permalink**) 03-21-11, 05:59

Quote:

Originally Posted by shammat

What about the ID column? Is that always the same for the same month?

Yes, it will be same..

shammat · #4 (**permalink**) 03-21-11, 06:03

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

mac4rfree · #5 (**permalink**) 03-21-11, 06:13

Quote:

Originally Posted by shammat

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!!!!

mac4rfree · #6 (**permalink**) 03-21-11, 06:16

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..

shammat · #7 (**permalink**) 03-21-11, 06:31

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?

mac4rfree · #8 (**permalink**) 03-21-11, 06:46

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..