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.
Function to add the Previous rows.
Moderator: NorbertKrupa
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Function to add the Previous rows.
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1
Re: Function to add the Previous rows.
Hi Raj!
Analytic Functions :: Cumulative SUM
Repeat several times to generate data:
Query:
Analytic Functions :: Cumulative SUM
Code: Select all
---
--- DDL
---
CREATE TABLE public.CumulativeSumExample
(
id IDENTITY,
amount INTEGER
);
Code: Select all
INSERT INTO public.CumulativeSumExample(amount) VALUES (randomint(100)); --- data generator
Code: Select all
SELECT id,
amount,
SUM(amount) OVER (ORDER BY id) AS 'CUMULATIVE SUM'
FROM public.CumulativeSumExample;
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Re: Function to add the Previous rows.
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..
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..
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1
Re: Function to add the Previous rows.
Ok...
Table
Data:
Query:
Table
Code: Select all
suse_db=> create table ExampleForRaj (year smallint not null, population int);
CREATE TABLE
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
>> \.
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)
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Re: Function to add the Previous rows.
Hi Daniel,
Thank uuuuuuuuuuuuu
Thank uuuuuuuuuuuuu
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1