Query to fetch multiple customer numbers for a single user in user table

Question

In our airline industry, we have a requirement to find out more than one customer number for a single login in AIR_USER table. The table structure is below.

ROW_ID	LOGIN	CUST_NUM	FULL_NAME
1-3EW	SUNILK	3001	SUNIL KUMAR
1-3QL	SUNILK	3002	SUNIL KUMAR
1-4FV	DILIPK	1001	DILEEP KUMAR
1-5TF	DILIPK	1003	DILEEP KUMAR
1-4BX	RAVIP	2002	RAVI PRADEEP

I used the below query, but it is taking huge time as cost and cardinality is into lakhs. Also, this table has almost million records as it contains all the user information.

SELECT 
  DISTINCT LOGIN, CUST_NUM, COUNT(CUST_NUM) 
FROM AIR_USER 
GROUP BY 
  LOGIN, CUST_NUM 
HAVING COUNT(CUST_NUM) > 1 
ORDER BY LOGIN;

Could you assist if this query is correct ? If correct, could you assist in refining the query.

Query to fetch multiple customer numbers for a single user in user table

Answer 1

Don't use DISTINCT and GROUP BY in the same (sub-) query. GROUP BY will return a single row per group so DISTINCT is pointless.

You also do not want to include CUST_NUM in the GROUP BY:

SELECT LOGIN,
       LISTAGG(CUST_NUM, ',') WITHIN GROUP (ORDER BY cust_num) AS cust_nums,
       COUNT(CUST_NUM) 
FROM   AIR_USER 
GROUP BY LOGIN
HAVING COUNT(CUST_NUM) > 1 
ORDER BY LOGIN

Which, for the sample data:

CREATE TABLE air_user (ROW_ID, LOGIN, CUST_NUM, FULL_NAME) AS
SELECT '1-3EW', 'AMAJAK', 3001, 'AMAJA KUMAR'  FROM DUAL UNION ALL
SELECT '1-3QL', 'AMAJAK', 3002, 'AMAJA KUMAR'  FROM DUAL UNION ALL
SELECT '1-4FV', 'BEEJAK', 1001, 'BEEJA KUMAR' FROM DUAL UNION ALL
SELECT '1-5TF', 'BEEJAK', 1003, 'BEEJA KUMAR' FROM DUAL UNION ALL
SELECT '1-4BX', 'CHARAP',  2002, 'CHARA PRADEEP' FROM DUAL;

Outputs:

LOGIN	CUST_NUMS	COUNT(CUST_NUM)
AMAJAK	3001,3002	2
BEEJAK	1001,1003	2

or, if you don't want to list the cust_num and only want to know the login you can simplify the query to:

SELECT LOGIN
FROM   AIR_USER 
GROUP BY LOGIN
HAVING COUNT(CUST_NUM) > 1 
ORDER BY LOGIN

Outputs:

LOGIN
AMAJAK
BEEJAK

fiddle