Home » SQL & PL/SQL » SQL & PL/SQL » calculate cummulative frequency (oracle 8i)
calculate cummulative frequency [message #434231] Mon, 07 December 2009 23:38 Go to next message
shaz
Messages: 182
Registered: June 2009
Senior Member
Is it possible to calculate cummulative frequency without the use of a cursor???

Consider the following test table:

CREATE TABLE TEST_SUM 
(ID NUMBER primary key not null,
DEPT VARCHAR2(5),
VALUE NUMBER);

insert into test_sum values(1,'a',10);
insert into test_sum values(2,'a',60);
insert into test_sum values(3,'a',19);
insert into test_sum values(4,'a',13);
insert into test_sum values(5,'b',10);
insert into test_sum values(6,'b',15);
insert into test_sum values(7,'c',17);
insert into test_sum values(8,'c',9);
insert into test_sum values(9,'d',155);
insert into test_sum values(10,'e',15);
insert into test_sum values(11,'e',17);

select dept,sum(value)as val from test_sum
group by dept;

dept val
a    102
b    25
c    26
d    155
e    32



i want to calculate cummulative frequency of val. ie.
dept val   cf
a    102   102
b    25    127
c    26    153
d    155   308
e    32    340


please help.
Re: calculate cummulative frequency [message #434234 is a reply to message #434231] Tue, 08 December 2009 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select dept, sum(sum(value)) over (order by dept) val
  2  from test_sum
  3  group by dept
  4  order by dept
  5  /
DEPT         VAL
----- ----------
a            102
b            127
c            153
d            308
e            340

5 rows selected.

Works if your 8i is >= 8.1.6.
This is why you have to post your version with 4 decimals.

Regards
Michel

[Edit: missing a letter]

[Updated on: Tue, 08 December 2009 03:41]

Report message to a moderator

Re: calculate cummulative frequency [message #434252 is a reply to message #434234] Tue, 08 December 2009 02:57 Go to previous message
shaz
Messages: 182
Registered: June 2009
Senior Member
Thanks Michel. Smile
Dont know why this didnt clicked me. May be I require some days off. Confused
Previous Topic: Getting session tree lock
Next Topic: Insert column value by select statement ..
Goto Forum:
  


Current Time: Wed Dec 07 18:25:08 CST 2016

Total time taken to generate the page: 0.17257 seconds