CODEWITHSUNDEEP
sqloracle-database
Vaibhav Gupta
Vaibhav Gupta

Reputation: 351

Finding maximum value from each group set in Oracle

I have below scenario: Input Data:

 ---------------------------------------------------------------
| ID    | Account   | Sub_Acct  | Email                         |
 ---------------------------------------------------------------
| 100   | AD        | AD1       | [email protected]                   |
| 100   | AB        | AB1       | [email protected], [email protected]     |
| 100   | AB        | AB2       | [email protected], [email protected]     |
| 200   | CD        | CD1       | [email protected], [email protected]   |
| 200   | AB        | AB1       | [email protected]                |
| 200   | CD        | CD2       | [email protected], [email protected]   |
| 200   | AB        | AB2       | [email protected]                   |
| 200   | CD        | CD3       | [email protected], [email protected]   |
 ---------------------------------------------------------------

I need to take count of individual accounts partitioned by IDs. Whichever account has maximum count, I want to display that respective account with single entry with column Sub_acct populated as NULL. Rest all other accounts should be populated with their respective Sub_acct values within that specific ID.

Email domains to be extracted from Email column. The email_domain column values of the secondary accounts (within specific ID) will have values from the primary account( i.e. maximum count).. Below is the expected output:

 ------- -------------------------------------------
| ID    | Account   | Sub_Acct  | Email_Domain      |
 ------- -------------------------------------------
| 100   | AB        |           | abc.com, xyz.com  |   
| 100   | AD        | AD1       | abc.com, xyz.com  |
| 200   | CD        |           | pqr.com, abc.com  |
| 200   | AB        | AB1       | pqr.com, abc.com  |
| 200   | AB        | AB2       | pqr.com, abc.com  |
 ---------------------------------------------------

I have edited this question. Sorry for the trouble caused. Can someone pls help with the sql query in Oracle. Thanks in advance.

Upvotes: 0

Views: 91

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

In your table an ID/Account pair can occur multiple times. And in your example such a pair has always the same Email. Is this guaranteed to be so? If it is, then your table isn't normalized and you can get consistency problems in the future.

However, relying on this would make writing the query easy-peasy:

select
  t.id,
  t.account,
  t.email,
  s.primary_account,
  s.primary_email
from mytable
join
(
  select
    id,
    stats_mode(account) as primary_account,
    stats_mode(email) as primary_email
  from mytable
  group by id
) s on s.id = t.id
order by id, account;

(It would be even easier, supported Oracle STATS_MODE OVER, but it doesn't yet.)

Upvotes: 0

Isolated
Isolated

Reputation: 6454

This works in Oracle 10...

    with rank1 as
    (
        select id, 
        account, 
        email, count(account) as account_count, 
        rank() over (partition by id order by count(account) desc) as order_rank
        from table1
        group by id, account, email
    )
    select t1.*, 
    r1.account as primary_account, 
    r1.email as primary_email
    from table1 t1
    join rank1 r1
      on r1.id = t1.id
    where r1.order_rank = 1

Upvotes: 2

Related Questions