Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to do cumulative sum for this scenario?

672680Mar 30 2011 — edited Mar 30 2011
Hi all,

I want to do cumulative sum in scenario below. my DB version: 10.2.0
the table and data: 
CREATE TABLE T (DT DATE, USR VARCHAR2(3));

Insert into T (DT,USR) values (to_date('2011-03-26','YYYY-MM-DD'),'a');
Insert into T (DT,USR) values (to_date('2011-03-26','YYYY-MM-DD'),'b');
Insert into T (DT,USR) values (to_date('2011-03-27','YYYY-MM-DD'),'b');
Insert into T (DT,USR) values (to_date('2011-03-27','YYYY-MM-DD'),'c');
Insert into T (DT,USR) values (to_date('2011-03-28','YYYY-MM-DD'),'c');
Insert into T (DT,USR) values (to_date('2011-03-28','YYYY-MM-DD'),'d');
Insert into T (DT,USR) values (to_date('2011-03-29','YYYY-MM-DD'),'d');
Insert into T (DT,USR) values (to_date('2011-03-29','YYYY-MM-DD'),'e');
the result I want is : 
dt                        distinct user
-------------------------------------
2011-03-26  	2
2011-03-27  	3
2011-03-28  	4
2011-03-29  	5
the only query I came up with is as below and it looks clumsy.
So, I wonder if there is a better way to do it?
SELECT a.dt, COUNT(DISTINCT b.usr)
FROM
  (SELECT DISTINCT DT FROM T ) A,
  T B
WHERE A.DT>=b.dt
GROUP BY a.dt;
OVER() seems not work, because oracle doesn`t allow COUNT(DISTINCT) be used together with OVER(ORDER BY ).

correct me if I am wrong.

Thanks

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 27 2011
Added on Mar 30 2011
5 comments
2,631 views