CODEWITHSUNDEEP
sqloracle-database
Americo
Americo

Reputation: 919

SQL - Grouping results by totaling

The below query gives me an output of two columns:

Day Num_Users_Retained
0   209312
1   22139
2   11457

And so on, all the way to 259 (Every day in the year 2012)..However I want day 0 to include the sum of all the values in num_users_retained from 0 to 259...and then I want day 1 to include the sum of all values from 1-259, and so on until I get to the last day. Here is the original query:

--Retention since January 1,2012--
select retention as Day,count(retention) as Num_Users_Retained
from (select player_id,round(init_dtime-create_dtime,0) as retention
from player
where Trunc(Create_Dtime) >= To_Date('2012-jan-01','yyyy-mon-dd')
and init_dtime is not null)
Group by retention
order by 1

Any suggestions?

Upvotes: 1

Views: 118

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27251

select retention as Day
     , Sum(count(retention)) over(order by retention desc) as Num_Users_Retained
 from (select player_id
            , round(init_dtime-create_dtime,0) as retention
        from player
       where Trunc(Create_Dtime) >= To_Date('2012-jan-01','yyyy-mon-dd')
         and init_dtime is not null
       )
Group by retention
order by retention

Upvotes: 1

Geordee Naliyath
Geordee Naliyath

Reputation: 1859

Use Analytic functions

select
    day,
    num_users_retained,
    sum(num_users_retained) over (order by day) as total_num_users_retained
from churn

http://www.sqlfiddle.com/#!4/24e02/1

This query is written over your resultset. You may be able to apply this to your original query.

Upvotes: 2

Related Questions