CODEWITHSUNDEEP
sqloracle-database
Americo
Americo

Reputation: 919

Oracle SQL -- When a Case query becomes more useful than running two separate querys

I have the below query:

--Total DLs--
select sum(DLs) as DLs_Total
From
 (
Select
Count(T.Create_Dtime) As Dls
From Player_Tapjoy T 
Inner Join Player P On T.Player_Id=P.Player_Id
Where P.ReferredBy IS NULL
Union All
Select
Count(Pt.Create_Dtime) As DLs
From Player_Aux_Pt Pt
Inner Join Player P On Pt.Player_Id=P.Player_Id
Where
Pt.Site = 'AppCircle'
And P.ReferredBy IS NULL
)

I'm going to run this report twice, once for "And P.ReferredBy is Null", and once for "And P.ReferredBy is NOT Null".

I feel like this may be a good time to switch over to case queries...so if in the case of null or not null, one query will produce two separate results?

Upvotes: 0

Views: 283

Answers (1)

RGO
RGO

Reputation: 4737

Yes, you can use case here. I have not tested the following query, but please check see if it works:

--Total DLs--
select sum(DLs_ReferredBy_Null) as DLs_ReferredBy_Null_Total, sum(DLs_ReferredBy_NotNull) as DLs_DLs_ReferredBy_NotNull_Total
From
 (
Select
Count(CASE WHEN P.ReferredBy IS NULL THEN T.Create_Dtime END) As DLs_ReferredBy_Null,
Count(CASE WHEN P.ReferredBy IS NOT NULL THEN T.Create_Dtime END) As DLs_ReferredBy_NotNull
From Player_Tapjoy T 
Inner Join Player P On T.Player_Id=P.Player_Id
Union All
Select
Count(CASE WHEN P.ReferredBy IS NULL THEN Pt.Create_Dtime END) As DLs_ReferredBy_Null,
Count(CASE WHEN P.ReferredBy IS NOT NULL THEN Pt.Create_Dtime END) As DLs_ReferredBy_NotNull
From Player_Aux_Pt Pt
Inner Join Player P On Pt.Player_Id=P.Player_Id
Where
Pt.Site = 'AppCircle'
)

Upvotes: 1

Related Questions