CODEWITHSUNDEEP
sqloracle-database
Americo
Americo

Reputation: 919

SQL Totaling by Day

I have a simple query that spits out new_users by day...

Select trunc(create_dtime),Count(Player_Id) as New_Users
  From Player
 Where Trunc(Create_Dtime) >= To_Date('2011-APR-22','yyyy-mon-dd')
 Group By Trunc(Create_Dtime)
 Order by trunc(Create_Dtime)

I would like this to total the new_users as each day goes on...so for example if with the current query spits out a result with:

Apr 22
Apr 23
Apr 24 of 1000 new users each day...

I want this new query to say:

Apr 22 = 1000 New users
Apr 23 = 2000
Apr 24 = 3000

...and so on until the very last date(today) has the total number of new users in one single row.

Let me know if this is clear enough.

Upvotes: 2

Views: 161

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270893

I would do this using a cumulative sum, which Oracle happily supports through the analytic functions:

select thedate, New_Users,
       sum(New_Users) over (order by thedate) as Cumulative_New_Users
from (Select trunc(create_dtime) as thedate, Count(Player_Id) as New_Users
      From Player
      Where Trunc(Create_Dtime) >= To_Date('2011-APR-22','yyyy-mon-dd')
      Group By Trunc(Create_Dtime)
     ) t
Order by 1

Upvotes: 1

Related Questions