Re: Is there a slick and efficient way to do this query?
Perhaps you should try...
create temp table test_it(days integer);
insert into test_it values(1);
insert into test_it values(2);
insert into test_it values(3);
insert into test_it values(4);
insert into test_it values(5);
insert into test_it values(6);
select
a.days,
(select sum(b.days) from test_it b),
(select sum(c.days) from test_it c where c.days <= a.days)
from
test_it a;
Output is:
days (expression) (expression)
1 21 1
2 21 3
3 21 6
4 21 10
5 21 15
6 21 21
If physical ordering is required of the fields in the first table (the one
that holds days), then you would have to also have a serial field and
change the SQL above accordingly.
HTH
Rick Pearson.
Please respond to no_spam@no_spam.com
To: informix-list@iiug.org
cc: (bcc: Rick Pearson/JMJ/AU)
Subject: Is there a slick and efficient way to do this query?
Suppose I have a table with an integer column called days.
table mytable
(
days integer
);
Based on this I want to create another table
table summarytable
(
days integer,
total_days integer,
cumulative_days integer
);
As weird as it seems, days is a unique identifier and there will be one row
for each distinct days in the first table, total_days is the sum of the
column days in the whole table. cumulative_days is the sum so far for up to
that number of days
For example
Days Total_Days Cumulative Days
2 10 2
3 10 7
5 10 10
Please don?t worry about why it?s being done this way. I have to do it.
Can anyone think of a slick and efficient way to do it, just using SQL?
Remember the differing values that days takes on in the first table is an
unknown.