funciones - having sql

¿Por qué no puede mezclar valores agregados y valores no agregados en un solo SELECT?

Question

Sé que si tiene una función agregada en una instrucción SELECT, entonces todos los demás valores de la declaración deben ser funciones agregadas o enumeradas en una cláusula GROUP BY. No entiendo por qué ese es el caso.

Si lo hago:

SELECT Name, ''Jones'' AS Surname FROM People

Yo obtengo:

NAME SURNAME
Dave Jones
Susan Jones
Amy Jones

Por lo tanto, el DBMS ha tomado un valor de cada fila y le ha agregado un único valor en el conjunto de resultados. Esta bien. Pero si eso funciona, ¿por qué no puedo hacerlo?

SELECT Name, COUNT(Name) AS Surname FROM People

Parece la misma idea, tome un valor de cada fila y agregue un solo valor. Pero en lugar de:

NAME SURNAME
Dave 3
Susan 3
Amy 3 

Yo obtengo:

Intentó ejecutar una consulta que no incluye la expresión especificada ''ContactName'' como parte de una función agregada.

Sé que no está permitido, pero las dos circunstancias parecen tan similares que no entiendo por qué. ¿Es para hacer que el DBMS sea más fácil de implementar? Si alguien me puede explicar por qué no funciona como creo que debería, estaría muy agradecido.

Answer 1

Como explicaron otros, cuando tiene un GROUP BY o está utilizando una función agregada como COUNT() en la lista SELECT , está haciendo una agrupación de filas y, por lo tanto, está agrupando filas en una para cada grupo.

Cuando solo usa funciones agregadas en la lista SELECT , sin GROUP BY , piense en ello como si tuviera un GROUP BY 1 , de modo que todas las filas se agrupen, se colapsen en una sola. Por lo tanto, si tiene cien filas, la base de datos no puede mostrarle un nombre, ya que hay cientos de ellas.

Sin embargo, para RDBMS que tienen funciones de "ventana", lo que desea es factible. Por ejemplo, usar funciones agregadas sin un GROUP BY .

Ejemplo para SQL Server, donde se cuentan todas las filas (nombres) en la tabla:

SELECT Name
, COUNT(*) OVER() AS cnt
FROM People

¿Cómo funciona lo anterior?

• Muestra el Name como COUNT(*) OVER() AS cnt no existía y

• Muestra la COUNT(*) como si estuviera haciendo una agrupación total de la tabla.

Otro ejemplo. Si tiene un campo de Surname en la tabla, puede tener algo como esto para mostrar todas las filas agrupadas por Apellido y contar cuántas personas tienen el mismo Apellido:

SELECT Name
, Surname
, COUNT(*) OVER(PARTITION BY Surname) AS cnt
FROM People

Answer 2

La función agregada toma valores de varias filas con una condición específica y los combina en un solo valor. Esta condición es definida por el GROUP BY en su declaración. Así que no puedes usar una función agregada sin un GROUP BY

Con

SELECT Name, ''Jones'' AS Surname FROM People 

simplemente selecciona una columna adicional con un valor fijo ... pero con

SELECT Name, COUNT(Name) AS Surname FROM People GROUP BY Name

le indica al DBMS que seleccione los nombres, recuerde la frecuencia con la que cada nombre ocurrió en la tabla y los colapsa en una fila. Entonces, si omite el GROUP BY el DBMS no se puede decir, cómo contraer los registros

Answer 3

La función agregada y la cláusula por grupo no son cosas separadas, son partes de la misma cosa que aparecen en diferentes lugares en la consulta. Si desea agregar en una columna, debe indicar qué función usar para la agregación; Si desea tener una función de agregación, debe aplicarse sobre alguna columna.

Answer 4

Los agregados no funcionan en un resultado completo, solo trabajan en un grupo en un resultado.

Considere una tabla que contiene:

Person Pet
-------- --------
Amy Cat
Amy Dog
Amy Canary
Dave Dog
Susan Snake
Susan Spider

Si utiliza una consulta que se agrupa en Persona, dividirá los datos en estos grupos:

Amy:
Amy Cat
Amy Dog
Amy Canary
Dave:
Dave Dog
Susan:
Susan Snake
Susan Spider

Si utiliza un agregado, por ejemplo, el agregado de conteo, producirá un resultado para cada grupo:

Amy:
Amy Cat
Amy Dog
Amy Canary count(*) = 3
Dave:
Dave Dog count(*) = 1
Susan:
Susan Snake
Susan Spider count(*) = 2

Por lo tanto, la consulta select Person, count(*) from People group by Person le da un registro para cada grupo:

Amy 3
Dave 1
Susan 2

Si intenta obtener también el campo Mascota en el resultado, eso no funciona porque puede haber varios valores para ese campo en cada grupo.

(Algunas bases de datos, como MySQL, lo permiten de todos modos, y solo devuelve cualquier valor aleatorio dentro del grupo, y es su responsabilidad saber si el resultado es razonable o no).

Si usa un agregado, pero no especifica ninguna agrupación, la consulta seguirá agrupada y el resultado completo es un solo grupo. Por lo tanto, el select count(*) from Person consultas select count(*) from Person creará un solo grupo que contiene todos los registros, y el agregado puede contar los registros de ese grupo. El resultado contiene una fila de cada grupo y, como solo hay un grupo, habrá una fila en el resultado.

Answer 5

Piénselo de esta manera: cuando llama a COUNT sin agrupar, "colapsa" la tabla en un solo grupo, lo que hace imposible acceder a los elementos individuales dentro de un grupo en una cláusula de selección.

Aún puede obtener su resultado mediante una subconsulta o una unión cruzada:

 SELECT p1.Name, COUNT(p2.Name) AS Surname FROM People p1 CROSS JOIN People p2 GROUP BY p1.Name
SELECT Name, (SELECT COUNT(Name) FROM People) AS Surname FROM People

Answer 6

Su consulta implícitamente solicita diferentes tipos de filas en su conjunto de resultados, y eso no está permitido. Todas las filas devueltas deben ser del mismo tipo y tener el mismo tipo de columnas.

''SELECCIONAR nombre, apellido'' quiere devolver una fila por cada fila en la tabla.

''SELECT COUNT (*)'' desea devolver una sola fila combinando los resultados de todas las filas en la tabla.

Creo que tienes razón en que, en este caso, la base de datos podría hacer ambas consultas y luego copiar el resultado de ''SELECT COUNT (*)'' en cada resultado. Una de las razones para no hacer esto es que sería un éxito de rendimiento sigiloso: efectivamente estarías haciendo una auto-unión extra sin declararlo en ninguna parte.

Otras respuestas han explicado cómo escribir una versión de trabajo de esta consulta, por lo que no voy a entrar en eso.