Function to add the Previous rows.

[rajasekhart]

Hi,

I have a table containing data as follows.

Create table Population(Total_Population int, Year int)

Insert into Population (Total_Population, Year) VALUES
(2, 1990),
(31895, 1995),
(1, 1996),
(94, 1998),
(1, 1999),
(1479, 2000),
(3000, 2004)

select Total_Population , Year from Population;

Total_Population Year
2 "1990"
31895 "1995"
1 "1996"
94 "1998"
1 "1999"
1479 "2000"
3000 "2004"

Here i want to get the cumulative sum of total_Population in each year. (like sum(total_Population) <= the given year)
Eg: In the year 1990 sum(total_Population) = 2
In the year 1995 sum(total_Population) = 31895+2=31897
In the year 1996 sum(total_Population) = 1+31895+2=31898
and so on.....

I want the result set as follows

Total_Population Year Cumulative_Sum
2 "1990" 2
31895 "1995" 31897
1 "1996" 31898
94 "1998" .
1 "1999" .
1479 "2000" .
3000 "2004" and so on...

Do we have any function to get this ?
Please help me.

Thanks,
Raj.

[id10t]

Hi Raj!
Analytic Functions :: Cumulative SUM

Code: Select all

---
--- DDL
--- 
CREATE TABLE public.CumulativeSumExample
(
    id       IDENTITY,
    amount   INTEGER
);

Repeat several times to generate data:

Code: Select all

INSERT INTO public.CumulativeSumExample(amount) VALUES (randomint(100)); --- data generator

Query:

Code: Select all

SELECT id,
       amount, 
       SUM(amount) OVER (ORDER BY id) AS 'CUMULATIVE SUM' 
  FROM public.CumulativeSumExample;

[rajasekhart]

Hi Daniel,

Thanks for quick reply..

But my requirement is as follows.

Let us say the Population in 1990 be 500 and
Population in 1991 be 1000 and
Population in 1992 be 500 and
Population in 1993 be 1000

So i want to get the 'Cumulative sum' column as follows.

Population in 1990 = 500 and Cumulative sum in 1990 = 500
Population in 1991 = 1000 and Cumulative sum in 1991 = 1500 [500 (in 1990) + 1000 (in 1991)]
Population in 1992 = 500 and Cumulative sum in 1992 = 2000 [500 (in 1990) + 1000 (in 1991) + 500 (in 1992) ]
and so on..

Eg:

Year Total_Population Cumulative Sum

1990 500 500
1991 1000 1500
1992 500 2000

and so on..

[id10t]

Ok...
Table

Code: Select all

suse_db=> create table ExampleForRaj (year smallint not null, population int);
CREATE TABLE

Data:

Code: Select all

suse_db=> copy ExampleForRaj from stdin delimiter ',';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1990,500
>> 1991,2500
>> 1992,500
>> \.

Query:

Code: Select all

suse_db=> select "year", population, SUM(population) OVER (ORDER by "year") from ExampleForRaj;
 year | population | ?column? 
------+------------+----------
 1990 |        500 |      500
 1991 |       2500 |     3000
 1992 |        500 |     3500
(3 rows)

[rajasekhart]

Hi Daniel,


Thank uuuuuuuuuuuuu