More than one occurrence from table

February 14, 2023

create table customer (cif number, name varchar(20),mobile number);

insert into table customer values(121,'ANT',789);    
insert into table customer values(122,'ANT',789);   
insert into table customer values(123,'ENT',789);    
insert into customer values(124,'ENT',789);    
insert into customer values(125,'BEE',123);    
insert into customer values(126,'BEE',123);    
insert into customer values(127,'BRO',789);    
insert into customer values(128,'FIO',789);    
commit;

I want retrieve data from customer table based on name and mobile more than one occurrences.

Can anyone help me out

Result like

>Solution :

You can use COUNT() aggregation as Analytic function along with grouping by those columns through use of PARTITION BY clause as

SELECT cif, name, mobile
  FROM (SELECT c.*,
               COUNT(*) OVER (PARTITION BY name, mobile) AS cnt
          FROM customer c )
 WHERE cnt > 1

Demo