Oracle SQL- Getting "Distinct" values within a "CASE" query

Question

I have the following query in ORACLE SQL:

Select
Trunc(Cs.Create_Dtime),
Count(Case When Cs.Cs_Listing_Id Like '99999999%' Then (Cs.Player_Id) End) As Sp_Dau, 
Count(Case When Cs.Cs_Listing_Id Not Like '99999999%' Then (Cs.Player_Id) End) As Cs_Dau
From
Player_Chkin_Cs Cs
Where
Trunc(Cs.Create_Dtime) >= To_Date('2012-Jan-01','yyyy-mon-dd')
Group By Trunc(Cs.Create_Dtime)
Order By 1 ASC

I added "Distinct" just before "case" for each count. I just want to make sure that this only returns all of the distinct player_Ids in each case. Can some one confirm? Thank you! Here is the final query:

Select
Trunc(Cs.Create_Dtime),
Count(Distinct Case When Cs.Cs_Listing_Id Like '99999999%' Then (Cs.Player_Id) End) As      Sp_Dau,
Count(Distinct Case When Cs.Cs_Listing_Id Not Like '99999999%' Then (Cs.Player_Id) End)     As Cs_Dau
From
Player_Chkin_Cs Cs
Where
Trunc(Cs.Create_Dtime) >= To_Date('2012-Jan-01','yyyy-mon-dd')
Group By Trunc(Cs.Create_Dtime)
Order By 1 ASC;

Should be easy enough to confirm for yourself by examining the output, but yes, it should return the count of distinct Player_Id with Cs_Listing_id LIKE '99999999%'. — Michael Berkowski
– Michael Berkowski, Commented Sep 26, 2012 at 1:52
Mike- Thanks - I can't really confirm in the output since there is so much data. But appreciate the response. — Americo
– Americo, Commented Sep 26, 2012 at 1:54
@Stuav - If there's "too much data", that means you need to use a context with less data (say, a local copy of the database), with just the relevant rows needed to confirm the 'expected' behavior. — Clockwork-Muse
– Clockwork-Muse, Commented Sep 26, 2012 at 2:14

Kirill Leontev · Accepted Answer · 2012-09-26 03:37:45Z

A simple test case for you to prove count(distinct ... returns only distinct values:

11:34:09 HR@vm_xe> select department_id, count(*) from employees group by department_id order by 2 desc;      

DEPARTMENT_ID   COUNT(*)                                                                                      
------------- ----------                                                                                      
           50         45                                                                                      
           80         34                                                                                      
          100          6                                                                                      
           30          6                                                                                      
           60          5                                                                                      
           90          3                                                                                      
           20          2                                                                                      
          110          2                                                                                      
           40          1                                                                                      
           10          1                                                                                      
                       1                                                                                      
           70          1                                                                                      

12 rows selected.                                                                                             

Elapsed: 00:00:00.03                                                                                          
11:34:12 HR@vm_xe> select count(department_id) "ALL", count(distinct department_id) "DISTINCT" from employees;

       ALL   DISTINCT                                                                                         
---------- ----------                                                                                         
       106         11                                                                                         

1 row selected.                                                                                               

Elapsed: 00:00:00.02                                                                                          
11:34:20 HR@vm_xe>

Collectives™ on Stack Overflow

Oracle SQL- Getting "Distinct" values within a "CASE" query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related