Home » SQL & PL/SQL » SQL & PL/SQL » calculate cummulative frequency (oracle 8i)
calculate cummulative frequency Mon, 07 December 2009 23:38
 shaz Messages: 182Registered: 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
```

Re: calculate cummulative frequency [message #434234 is a reply to message #434231] Tue, 08 December 2009 00:39
 Michel Cadot Messages: 65084Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 shaz Messages: 182Registered: June 2009 Senior Member
Thanks Michel.
Dont know why this didnt clicked me. May be I require some days off.
 Previous Topic: Getting session tree lock Next Topic: Insert column value by select statement ..
Goto Forum:

Current Time: Sat Jul 22 12:03:25 CDT 2017

Total time taken to generate the page: 0.12005 seconds