Oracle - Filtering based on column value

Question

I have the following table for which I would like to obtain result based on the number of different TYPEs available for every CONTRACT_ID.

Table:

______________________________________________________________________________________
|Source             |   CONTRACT_ID |   TYPE        |   ADDRESS |   PHONE           |
______________________________________________________________________________________
|Source1            |   1234        |   INSURED     |   unique1 |   4321            |
______________________________________________________________________________________
|Source1            |   1234        |   INSURED     |   unique1 |   4321            |
______________________________________________________________________________________
|Source1            |   1234        |   DRIVER      |   unique1 |   4321            |
______________________________________________________________________________________
|Source1            |   1234        |   INSURED     |   unique1 |   4321            |
______________________________________________________________________________________
|Source2            |   2345        |   INSURED     |   unique2 |   5678            |
______________________________________________________________________________________
|Source2            |   2345        |   DRIVER      |   unique2 |   5678            |
______________________________________________________________________________________
|Source2            |   2345        |   CONTRACT    |   unique2 |   5678            |
______________________________________________________________________________________

Result:

______________________________________________________________________________________
|Source             |   CONTRACT_ID |   ROLES       |   ADDRESS |   PHONE           |
______________________________________________________________________________________
|Source1            |   1234        |   2           |   unique1 |   4321            |
______________________________________________________________________________________
|Source2            |   2345        |   3           |   unique2 |   5678            |
______________________________________________________________________________________

i.e, The ROLES column in the result table should be the number of different TYPEs for a distinct CONTRACT_ID.

Can anyone help?

I have surfed but still couldnt get any idea on how to count the number of different varchar values @KaushikNayak! — user8595948
– user8595948, Commented Sep 25, 2017 at 12:54

Kaushik Nayak · Accepted Answer · 2017-09-25 12:56:34Z

1

SELECT max(source) , contract_id ,
count( DISTINCT type) roles , max(address), max(phone) 
FROM Table group by contract_id ;

answered Sep 25, 2017 at 12:56

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2017-09-25 12:53:59Z

0

I think you are looking for count(distinct):

select Source, CONTRACT_ID, ADDRESS, PHONE, count(distinct type) as roles
from t
group by Source, CONTRACT_ID, ADDRESS, PHONE  ;

This is not "filtering". It is "aggregation".

answered Sep 25, 2017 at 12:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Oracle - Filtering based on column value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related