Oracle SQL Count distinct values in a certain column

Question

I am trying to query a table with a certain logic and I want to remove the records which have a count of 2 or more distinct values in PERSON_ID column. I cannot find an appropriate window query to achieve this. I already tried using:

SELECT 
CUSTOMER_ID, PERSON_ID, CODE,
DENSE_RANK() OVER (PARTITION BY CUSTOMER_iD, PERSON_ID ORDER BY PERSON_ID ASC) AS NR 
FROM TBL_1;

But I get the following result:

I want to achieve the result below, which counts the distinct values within PERSON_ID column based on a certain CUSTOMER_ID. In my case Customer "444333" would be a record which I want to remove because it has 2 distinct Person_Id's

eshirvana · Accepted Answer · 2021-07-02 14:42:17Z

2

here is what you need:

SELECT 
customer_id, count(distinct PERSON_ID) distinct_person_count
FROM TBL_1
group by customer_id

and if you want to show it for eahc row , you can join it again with the table :

select * from TBL_1 t
join (
     select customer_id, count(distinct PERSON_ID) distinct_person_count
     from TBL_1
     group by customer_id
) tt
on t.customer_id = tt.customer_id

note: you can't have distinct within window functions

answered Jul 2, 2021 at 14:42

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2021-07-02 15:29:23Z

0

If you want the distinct count on each row, then use a window function:

select t.*,
       count(distinct person_id) over (partition by customer_id)
from t;

Oracle does support distinct in window functions.

answered Jul 2, 2021 at 15:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Oracle SQL Count distinct values in a certain column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related