CODEWITHSUNDEEP
sqloracle-database
Lorik Berisha
Lorik Berisha

Reputation: 263

Oracle SQL Count distinct values in a certain column

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:

enter image description here

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

enter image description here

Upvotes: 0

Views: 3947

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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.

Upvotes: 0

eshirvana
eshirvana

Reputation: 24568

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

Upvotes: 2

Related Questions