CODEWITHSUNDEEP
sqloracle-database
Americo
Americo

Reputation: 919

Oracle SQL - Combine Two Results from Two Queries Into One

I have these two separate queries:

Query 1

select '2013-03-03' As "Week_Of",
count(player_id) as cohort_size
from player
where trunc(create_dtime) > To_Date('2013-Mar-03','yyyy-mon-dd')-7
and trunc(create_dtime) <= To_Date('2013-Mar-03','yyyy-mon-dd')
and world_flag != '1'
;

Which outputs:

Week_of      Cohort_size
2013-03-03      18183

Query 2

select '2013-03-03' As "Week_Of", 
count(player_id) as Day_0_Ret
from player
where trunc(init_dtime)-trunc(create_dtime) >= 0
and trunc(create_dtime) > To_Date('2013-Mar-03','yyyy-mon-dd')-7
and trunc(create_dtime) <= To_Date('2013-Mar-03','yyyy-mon-dd')
and world_flag != '1'
;

Which Outputs:

Week_of      Day_0_Ret
2013-03-03      15684

I want to bring these two queries together so I have one query that outputs:

Week_of         Cohort_Size     Day_0_Ret
2013-03-03       18183              15684

Upvotes: 1

Views: 1799

Answers (1)

DazzaL
DazzaL

Reputation: 21993

use a case statement to do a conditional count:

select '2013-03-03' As "Week_Of",
count(player_id) as cohort_size ,
count(case 
        when trunc(init_dtime)-trunc(create_dtime) >= 0 
        then player_id 
      end) as Day_0_Ret
from player
where trunc(create_dtime) > To_Date('2013-Mar-03','yyyy-mon-dd')-7
and trunc(create_dtime) <= To_Date('2013-Mar-03','yyyy-mon-dd')
and world_flag != '1'
;

Upvotes: 1

Related Questions