Oracle group/count query

Question

I have the following two tables:

TableOne
========
Id1|ColA1|ColB1|ColC1|ColD1|ColE1
--------------------------------
  1| AFoo|BFoo |CFoo | DFoo| EFoo
  2| AFoo|BBar |CFoo | DFoo| EFoo

TableTwo
========
Id2|ColA2|ColB2|ColC2
---------------------
 11| 1   |ABC  |NOP  |
 12| 1   |ABC  |QRS  |
 13| 1   |DEF  |TUV  |
 14| 1   |DEF  |WXY  | 
 15| 1   |DEF  |FGH  | 
 16| 2   |ABC  |NOP  | 

I run the following query:

select t1.*, t2.*
from TableOne t1
inner join TableTwo t2 on t2.ColA2=t1.Id1
where t1.ColA1='AFoo'

and get the following result:

Result
======
Id1|ColA1|ColB1|ColC1|ColD1|ColE1|Id2|ColA2|ColB2|ColC2
-------------------------------------------------------
  1| AFoo|BFoo |CFoo | DFoo| EFoo| 11| 1   | ABC | NOP
  1| AFoo|BFoo |CFoo | DFoo| EFoo| 12| 1   | ABC | QRS
  1| AFoo|BFoo |CFoo | DFoo| EFoo| 13| 1   | DEF | TUV
  1| AFoo|BFoo |CFoo | DFoo| EFoo| 14| 1   | DEF | WXY
  1| AFoo|BFoo |CFoo | DFoo| EFoo| 15| 1   | DEF | FGH
  2| AFoo|BBar |CFoo | DFoo| EFoo| 16| 2   | ABC | NOP

What I really want returned is:

Desired Result
======
Id1|MaxDup
----------------------------------------
  1| 3 (This is because there are 3 DEF records)
  2| 1 (This is because there is 1 ABC record)

So, I am trying to track the maximum number of occurrences in ColB2 which appears for each TableOne row. In the example above, the ID1 of 1 has two ABC records and three DEF records associated with it. Since there are more DEF records than ABC records, I want the count of DEF records returned.

Can anybody provide a working example that can demonstrate this?

Answer 1

Try this one (I haven't tested it):

with t2 as (
    select  ColA2, ColB2, count(*) cnt 
    from TableTwo
    group by ColA2, ColB2 
)
select t1.Id1,
(  select max(cnt) MaxDup
   from t2
   where t2.ColA2=t1.Id1)
from TableOne t1