CODEWITHSUNDEEP
sqloracle-databasejoinplsqlgroup-by
some_randomer
some_randomer

Reputation: 333

How do I get a count of records from one table with detail from another table

In Oracle I have two tables, USER and USER_DETAIL. The USER_DETAIL table has a foreign key link to the USER table, so pretty standard stuff. The USER table has among others USER_ID and ACCOUNT_NUM fields, and USER_DETAIL has further data such as USER_ID and IBAN.

The scenario is that multiple users can have the same IBAN. What I'm trying to do is create a query that shows where the same IBAN is being used by more than one user, with the number of users and the list of account numbers for each IBAN.

Db tables:

USER                    USER_DETAIL
________                ________
USER_ID    ACCOUNT_NUM  USER_ID    IBAN
1,         ACC001       1,         IBAN001
2,         ACC002       2
3,         ACC003       3,         IBAN002
4,         ACC004       4,         IBAN001

The query result I'm trying to achieve (showing that IBAN001 is being used by ACC001 and ACC004):

COUNT    IBAN    ACCOUNT_NUM
2        IBAN001 ACC001
                 ACC004

The part that's confusing me is the JOIN and GROUP BY. This works to get the count and IBAN:

SELECT COUNT(ud.user_id) AS num_users, ud.iban 
FROM user_detail ud
WHERE ud.iban IS NOT NULL
GROUP BY iban
HAVING COUNT(ud.user_id) > 1
ORDER BY ud.iban

But when I try to join to the USER table and show all account numbers using each IBAN, I either get a "not a GROUP BY expression" error or the count is lost:

SELECT COUNT(ud.user_id) AS num_users, ud.iban, u.account_num 
FROM user u 
INNER JOIN user_detail ud USING (user_id)
WHERE ud.iban IS NOT NULL
GROUP BY ud.iban, u.account_num
HAVING COUNT(ud.user_id) > 1
ORDER BY ud.iban

Upvotes: 0

Views: 359

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

If I understand correctly you want LISTAGG() to combine the accounts together in the aggregation results:

SELECT ud.iban, COUNT(*) as num_users,
       LISTAGG(u.account_num, ',') WITHIN GROUP (ORDER BY u.account_num)
FROM user u INNER JOIN
     user_detail ud
     USING (user_id)
WHERE ud.iban IS NOT NULL
GROUP BY ud.iban
HAVING COUNT(*) > 1
ORDER BY ud.iban

Upvotes: 1

Related Questions