sumar - sql contar registros agrupados

debe aparecer en la cláusula GROUP BY o debe usarse en una función agregada

Question

Tengo una mesa que se parece a esta llamada ''makerar''

 cname  | wmname |          avg           
--------+-------------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  | 1.00000000000000000000
 spain  | usopp  |     5.0000000000000000

Y quiero seleccionar la media máxima para cada cname.

SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

pero obtendré un error,

ERROR:  column "makerar.wmname" must appear in the GROUP BY clause or be used in an   aggregate function 
LINE 1: SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

entonces hago esto

SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname, wmname;

sin embargo, esto no dará los resultados intencionados, y se muestra el resultado incorrecto a continuación.

 cname  | wmname |          max           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  | 1.00000000000000000000
 spain  | usopp  |     5.0000000000000000

Los resultados reales deben ser

 cname  | wmname |          max           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

¿Cómo puedo solucionar este problema?

Nota: Esta tabla es una VISTA creada a partir de una operación anterior.

Answer 1

El problema de especificar campos no agrupados y no agregados en group by selecciones es que el motor no tiene forma de saber qué campo de registro debe devolver en este caso. ¿Es primero? ¿Es el último? Generalmente no hay registros que naturalmente correspondan al resultado agregado ( min Y max Son excepciones).

Sin embargo, hay una solución alternativa: hacer que el campo requerido se agregue también. En posgres, esto debería funcionar:

SELECT cname, (array_agg(wmname ORDER BY avg DESC))[1], MAX(avg)
FROM makerar GROUP BY cname;

Tenga en cuenta que esto crea una matriz de todas las wnames, ordenadas por avg, y devuelve el primer elemento (las matrices en postgres están basadas en 1).

Answer 2

En Postgres, también puede usar la sintaxis especial DISTINCT ON (expression) :

SELECT DISTINCT ON (cname) 
    cname, wmname, avg
FROM 
    makerar 
ORDER BY 
    cname, avg DESC ;

Answer 3

Esto parece funcionar tan bien

SELECT *
FROM makerar m1
WHERE m1.avg = (SELECT MAX(avg)
                FROM makerar m2
                WHERE m1.cname = m2.cname
               )

Answer 4

Recientemente me encontré con este problema, al tratar de contar el uso de mayús / case when , y encontré que al cambiar el orden de las instrucciones which y count soluciona el problema:

SELECT date(dateday) as pick_day,
COUNT(CASE WHEN (apples = ''TRUE'' OR oranges ''TRUE'') THEN fruit END)  AS fruit_counter

FROM pickings

GROUP BY 1

En lugar de usar, en este último, donde obtuve errores, las manzanas y las naranjas deberían aparecer en funciones agregadas

CASE WHEN ((apples = ''TRUE'' OR oranges ''TRUE'') THEN COUNT(*) END) END AS fruit_counter

Answer 5

Sí, este es un problema de agregación común. Antes de SQL3 (1999) , los campos seleccionados deben aparecer en la cláusula GROUP BY [*].

Para solucionar este problema, debe calcular el agregado en una subconsulta y luego unirlo consigo mismo para obtener las columnas adicionales que debe mostrar:

SELECT m.cname, m.wmname, t.mx
FROM (
    SELECT cname, MAX(avg) AS mx
    FROM makerar
    GROUP BY cname
    ) t JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg
;

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

Pero también puede usar funciones de ventana, que se ve más simple:

SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar
;

Lo único con este método es que mostrará todos los registros (las funciones de ventana no agrupan). Pero mostrará el MAX correcto (es decir, cname at cname level) para el país en cada fila, por lo que depende de usted:

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  |     5.0000000000000000
 spain  | usopp  |     5.0000000000000000

La solución, posiblemente menos elegante, para mostrar las únicas (cname, wmname) coinciden con el valor máximo, es:

SELECT DISTINCT /* distinct here matters, because maybe there are various tuples for the same max value */
    m.cname, m.wmname, t.avg AS mx
FROM (
    SELECT cname, wmname, avg, ROW_NUMBER() OVER (PARTITION BY avg DESC) AS rn 
    FROM makerar
) t JOIN makerar m ON m.cname = t.cname AND m.wmname = t.wmname AND t.rn = 1
;


 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

[*]: Curiosamente, aunque el tipo de especificación permite seleccionar campos no agrupados, parece que a los motores principales no les gusta. Oracle y SQLServer simplemente no permiten esto en absoluto. Mysql solía permitirlo de manera predeterminada, pero ahora desde 5.7 el administrador necesita habilitar esta opción ( ONLY_FULL_GROUP_BY ) manualmente en la configuración del servidor para que esta característica sea compatible ...

Answer 6

SELECT t1.cname, t1.wmname, t2.max
FROM makerar t1 JOIN (
    SELECT cname, MAX(avg) max
    FROM makerar
    GROUP BY cname ) t2
ON t1.cname = t2.cname AND t1.avg = t2.max;

Utilizando la función de ventana rank() :

SELECT cname, wmname, avg
FROM (
    SELECT cname, wmname, avg, rank() 
    OVER (PARTITION BY cname ORDER BY avg DESC)
    FROM makerar) t
WHERE rank = 1;

Nota

Cualquiera de los dos conservará múltiples valores máximos por grupo. Si solo desea un solo registro por grupo, incluso si hay más de un registro con promedio igual a máximo, debe verificar la respuesta de @ ypercube.