How do I find user with more keys in a table in Oracle?

Question

SELECT bau.username, cc.key
FROM ba_users bau
INNER JOIN billings ba
  ON ba.id = bau.billing_account_id
INNER JOIN accc_once_only_cc_map acooc
  ON acooc.billing_account_id = ba.id
INNER JOIN account_components_map acm
  on acooc.account_component_id = acm.account_component_id
INNER JOIN cuoffers cuo
  ON cuo.id = acm.customized_offer_id
INNER JOIN charges cc
  ON cc.key = acooc.charge_cluster_key;

result is for example

USERNAME        KEY
1182925980_0    RT_FEE
1182926277_0    RT_FEE
1182926574_0    RT_FEE
1182924785_0    RT_FEE
1182925980_0    RT_FEE2
1182923514_0    RT_FEE
1182932481_0    RT_FEE
1182936581_0    RT_FEE
1182941631_0    RT_FEE

now I would like to found which user has more then one key and print out the user with keys.

like:

1182925980_0

has keys

RT_FEE and RT_FEE2

How to do this with plsql?

score 4 · Accepted Answer · edited May 23 '17 at 12:11

The first part (finding users with > 1 key)

WITH CTE AS
(
  SELECT bau.username, cc.key
  FROM ba_users bau
  INNER JOIN billings ba
    ON ba.id = bau.billing_account_id
  INNER JOIN accc_once_only_cc_map acooc
    ON acooc.billing_account_id = ba.id
  INNER JOIN account_components_map acm
    on acooc.account_component_id = acm.account_component_id
  INNER JOIN cuoffers cuo
    ON cuo.id = acm.customized_offer_id
  INNER JOIN charges cc
    ON cc.key = acooc.charge_cluster_key
)
SELECT username, COUNT(*)
  FROM CTE
  GROUP BY username
  HAVING COUNT(*) > 1;

The second part of your query (showing a list of all keys projected in a single column) will will depend on whether you can use listagg or not.

WITH CTE AS
(
  SELECT bau.username, cc.key
  FROM ba_users bau
  INNER JOIN billings ba
    ON ba.id = bau.billing_account_id
  INNER JOIN accc_once_only_cc_map acooc
    ON acooc.billing_account_id = ba.id
  INNER JOIN account_components_map acm
    on acooc.account_component_id = acm.account_component_id
  INNER JOIN cuoffers cuo
    ON cuo.id = acm.customized_offer_id
  INNER JOIN charges cc
    ON cc.key = acooc.charge_cluster_key
)
SELECT username, LISTAGG(key, ', ') WITHIN GROUP (ORDER BY key) the_keys
  FROM CTE
  GROUP BY username
  HAVING COUNT(*) > 1;

score 0 · Answer 2 · answered Jan 27 '14 at 10:57

0

You can try something like this:

SELECT username,key,COUNT(*)
FROM ba_users
GROUP BY username,key
HAVING COUNT(*) > 1

This should display the username with more than one key, hope this helps.

answered Jan 27 '14 at 10:57

XcisioN

107
9

Do you need vote? your answer is wrong,how you get votes for wrong answer? – Hamidreza Jan 27 '14 at 11:02
3

Grouping by username AND key will only show instances where the same user has the **same** key more than once. – StuartLC Jan 27 '14 at 11:02

score 0 · Answer 3 · answered Jan 27 '14 at 10:59

try this query:

SELECT username,COUNT(username) FROM
(SELECT bau.username, cc.key
FROM ba_users bau
INNER JOIN billings ba
  ON ba.id = bau.billing_account_id
INNER JOIN accc_once_only_cc_map acooc
  ON acooc.billing_account_id = ba.id
INNER JOIN account_components_map acm
  on acooc.account_component_id = acm.account_component_id
INNER JOIN cuoffers cuo
  ON cuo.id = acm.customized_offer_id
INNER JOIN charges cc
  ON cc.key = acooc.charge_cluster_key)t1 
GROUP BY username
HAVING(COUNT(username) > 1);

How do I find user with more keys in a table in Oracle?

3 Answers3