Question:
I want to make a query that in theory (and wrong for practical purposes) would be something like this
select A,MAX(COUNT(B)) from Tabla group by A;
However I know this doesn't work. For MAX(count(op)) to work, you would have to put it alone, that is, it would be something like this:
select MAX(COUNT(OP)) from Tabla group by A;
However, I require the A attribute associated with MAX(…).
How could I do it? Doing something analogous to the first thing I get the following error:
ORA-00937: not a single-group group function
Answer:
As the error tells you, you have two nested group functions.
You don't need to do two nested queries, you can group by counting:
select A, COUNT(B) from tabla group by A;
and the result you order:
select A, COUNT(B) as cantidad from tabla group by A order by cantidad;
and you keep only the first:
select A, COUNT(B) as cantidad
from tabla
group by A
order by cantidad desc
fetch first row only;