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

Answer 2

It's SQL Server code, but I think it can be safely transferred to Oracle:

    select  id1,cola1,colb1,colc1,cold1,cole1,max(dup) as dup2  from (
        select t1.id1, t1.cola1, t1.colb1, t1.colc1,t1.cold1,t1.cole1, t2.colb2,count(*) as dup
        from t1 
        inner join  t2  on t2.cola2=t1.Id1
        where t1.cola1='Afoo'
        group by t1.id1, t1.cola1, t1.colb1, t1.colc1,t1.cold1,t1.cole1, t2.colb2) 
    tab
    group by id1,cola1,colb1,colc1,cold1,cole1

It works on Sql Server, and by enveloping in a subquery you can extract only id1 and dup2. Tried expanding your dataset, it works.

Answer 3

Here is a result based on your test data.

SQL> select       
        cola2
        , cnt as maxDup
from (        
        select       
                cola2
                , colb2
                , cnt
                , rank() over (partition by cola2 order by cnt desc ) cnt_rnk
        from (
                select cola2
                       , colb2
                       , count(*) as cnt
                from  TableTwo
                group by cola2, colb2
        )
    )        
where     cnt_rnk = 1
/
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19  
COLA     MAXDUP
---- ----------
1             3
2             1

SQL>

The innermost query counts the duplicate. The middle query ranks the counts of duplicates. The outermost query filters for the combo with the highest count:

Answer 4

Maybe this one?

SELECT t2.id2 id, count(t2.ColB2) MaxDup
FROM TableTwo t2
WHERE EXISTS(
      SELECT * FROM TableOne t1
      WHERE t1.id1 = t2.id2
      AND t1.ColA1='AFoo')
AND rownum < 2
GROUP BY t2.id2, t2.ColB2
ORDER BY MaxDup desc;