Home / Questions / 272129
Accepted
k1k4ss0
Asked: 2020-06-15 07:30:09 +0800 CST 2020-06-15 07:30:09 +0800 CST

how to make a query with MAX(count) and another attribute

  • 772

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
sql

2 Answers

  1. Best 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;
    • 4

  2. An alternative to Paul's proposal could be:

    WITH CTE AS (
  SELECT A, 
         COUNT(B) Cant
         FROM tabla
         GROUP BY A
 )
 SELECT *
        FROM CTE T1
        WHERE CANT = (SELECT MAX(CANT) FROM CTE)
;

    It is a bit more complex, but it has two interesting points:

    • It is a "pure SQL" solution, it should be compatible with almost any database, that at least implements "common table expression"
    • In case you had more than one Awith the same maximum, this query will return all of them.
    • 1