I need to get ID and person name from the below table where address1, address2, state, city, country columns have same data for all the roles.

I want output as

select ID, Name, count(*) from person group by address1,address2,city,state having count(*) > 1;

which is giving the exception : not a group by expression.

Please suggest a better approach to achieve about output.

CodePudding user response：

You can use analytic functions:

SELECT ID, Name, Address1, Address2, State, City
FROM   (
  SELECT p.*,
         COUNT(DISTINCT Name) OVER (PARTITION BY Address1, Address2, State, City)
           AS cnt,
         ROW_NUMBER() OVER (
           PARTITION BY Address1, Address2, State, City, Name
           ORDER BY id
         ) AS rn
  FROM   person p
)
WHERE  cnt > 1
AND    rn = 1;

Which, for the sample data:

CREATE TABLE person (ID, Name, Address1, Address2, State, City) AS
SELECT 1, 'AB', 'AB@123', 'AB@345', 'st1', 'ct1' FROM DUAL UNION ALL
SELECT 2, 'AB', 'AB@123', 'AB@345', 'st1', 'ct1' FROM DUAL UNION ALL
SELECT 3, 'CD', 'AB@123', 'AB@345', 'st1', 'ct1' FROM DUAL UNION ALL
SELECT 4, 'EF', 'EF@123', 'AB@345', 'st1', 'ct1' FROM DUAL;

Outputs:

ID	NAME	ADDRESS1	ADDRESS2	STATE	CITY
1	AB	AB@123	AB@345	st1	ct1
3	CD	AB@123	AB@345	st1	ct1

db<>fiddle here