en la lección de hoy, aprenderá sobre cómo agrupar los resultados devueltos por sus consultas utilizando la cláusula GROUP BY de SQL.
los objetivos de la lección de hoy son:
- Aprender a agrupar resultados usando GROUP BY
- usar funciones de agregado para realizar cálculos
- entender cómo filtrar grupos usando la cláusula HAVING
¡importante! Por favor, siga y haga los ejemplos en su base de datos. Si aún no lo ha hecho, regístrese en mi guía Para comenzar con SQL Server., Obtendrá instrucciones sobre cómo instalar las herramientas gratuitas y la base de datos de muestra.
cláusula SQL GROUP BY
la cláusula SQL GROUP BY se utiliza para generar una fila a través de valores de columna especificados. Normalmente se usa junto con funciones agregadas como suma o recuento para resumir valores. En SQL los grupos son combinaciones únicas de campos. En lugar de devolver todas las filas de una tabla, cuando los valores se agrupan, solo se devuelven las combinaciones únicas.
la cláusula GROUP BY se añade a la instrucción SQL después de la cláusula WHERE., Aquí hay un ejemplo donde estamos listando SalesOrderID, excluyendo cantidades mayores de 100.
Hay un par de cosas a tener en cuenta. Primero, las columnas que queremos resumir se enumeran, separadas por comas, en la cláusula GROUP BY. En segundo lugar, esta misma lista de columnas debe aparecer en la instrucción select; de lo contrario, la instrucción fallará.,
cuando se ejecuta esta instrucción, no se devuelven todas las filas filtradas. En el resultado solo se incluyen combinaciones únicas de SalesOrderID. Esta declaración es muy similar a
Pero hay una diferencia clave., El modificador distinto se detiene en la salida de una combinación única de filas, mientras que, con la instrucción GROUP BY, podemos calcular valores basados en las filas filtradas subyacentes para cada combinación única.
En otras palabras, usando nuestro ejemplo, con el grupo por, podemos calcular el número U OrderDetails por orden de la siguiente manera:
COUNT es un ejemplo de una función agregada, estos son los que realmente dan al grupo por Declaración su valor especial.
SQL aggregate Functions
algunas funciones, como SUM, se utilizan para realizar cálculos en un grupo de filas, estas se denominan funciones aggregate. En la mayoría de los casos, estas funciones operan en un grupo de valores que se definen utilizando la cláusula GROUP BY., Cuando no hay una cláusula GROUP BY, generalmente se entiende que la función aggregate se aplica a todos los resultados filtrados.
Algunos de los más comunes funciones de agregado son:
| AVG(expresión) | Calcular el promedio de la expresión. |
| COUNT (expresión) | Count ocurrencias de valores no nulos devueltos por la expresión. |
| COUNT ( * ) | cuenta todas las filas de la tabla especificada., |
| MIN (expresión) | encuentra el valor mínimo de expresión. |
| MAX(expresión) | Encuentra el máximo valor de la expresión. |
| SUM(expresión) | Calcular la suma de la expresión. |
Estas funciones se pueden utilizar en conjunción con la cláusula GROUP BY. Por su cuenta, operan en toda la tabla; sin embargo, cuando se usan con GROUP BY, sus cálculos se «restablecen» cada vez que cambia el agrupamiento., De esta manera actúan como subtotales.
simple Aggregate Function
al usar la función aggregate, puede calcular el resultado en todos los valores o en valores distintos. Por ejemplo, para contar todos los registros de SalesOrderDetails podríamos usar la expresión:
SELECT COUNT(SalesOrderID)
FROM Sales.SalesOrderDetail
esto resulta en un recuento de 121317 que se devuelve.
para contar los distintos pedidos que componen los detalles usaríamos lo siguiente:
SELECT COUNT(Distinct SalesOrderID)
FROM Sales.SalesOrderDetail
el recuento ahora es 31465.,
SQL GROUP BY and Aggregate Functions
to aggregate significa hacer todo a partir de partes individuales. Las funciones agregadas son funciones que trabajan en más de una fila para devolver un resultado.
AVG y SUM
la función SUM suma los valores devueltos, de manera similar AVG calcula el promedio.
veamos si podemos calcular el importe total del pedido a partir de los detalles del pedido., De lecciones anteriores sabemos cómo calcular la cantidad total para cada detalle como:
SELECT SalesOrderID,
ProductID,
OrderQty* UnitPrice As ExtendedPrice
FROM Sales.SalesOrderDetail
dado que podemos aplicar la función de agregado a las expresiones, podemos configurar una agrupación en OrderID para calcular el precio total por pedido como
SELECT SalesOrderID, SUM(OrderQty * UnitPrice) AS TotalPriceFROM Sales.SalesOrderDetailGROUP BY SalesOrderID
incluso podemos Ordenar por el total para obtener los primeros pedidos
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY TotalPrice DESC
de manera similar podemos calcular el detalle promedio del pedido utilizando AVG. ¿Por qué lo intentas a continuación! Si te quedas atascado, cambia la respuesta.
para los curiosos, ya que un promedio se calcula como la suma de la muestra dividida por el recuento de la muestra, entonces usando AVG en la declaración anterior es lo mismo que:
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) / COUNT(SalesOrderID) AS AvgOrderAmount FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
hemos cubierto mucho en esta sección., Estos son algunos puntos clave para recordar:
- Una función aggregate puede evaluar una expresión como SUM(A + B)
- Debe alias funciones aggregate, por lo que los nombres de columna son significativos
- Cuando se trabaja con funciones aggregate y GROUP BY, A veces es más fácil pensar en los detalles primero, es decir, escribir una simple instrucción SELECT, inspeccionar los resultados, luego agregar las cosas elegantes. Cubro esto en cómo escribir mejores consultas.
SQL COUNT
la función COUNT se utiliza cuando necesita saber cuántos registros existen en una tabla o dentro de un grupo., COUNT(*) contará todos los registros de la agrupación; mientras que COUNT(expression) cuenta todos los registros donde el resultado de la expresión no es nulo. También puede usar Distinct with COUNT para encontrar el número de valores únicos dentro de un grupo.
para encontrar el número de líneas SalesOrderDetail por pedido
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
para encontrar el número de órdenes de venta únicas por producto
SELECT ProductID,
COUNT(DISTINCT SalesOrderID)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
MIN y MAX
Use MIN y MAX para encontrar los valores más pequeños y más grandes, respectivamente, dentro de una tabla o grupo.,
por ejemplo, para encontrar las cantidades más pequeñas y más grandes de productos pedidos dentro de un pedido, intente
SELECT SalesOrderID,
Min(OrderQty) AS MinQuantity,
Max(OrderQty) AS MaxQuantity
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
También puede encontrar el valor mínimo o máximo de un cálculo. Aquí encontramos la mayor cantidad de productos pedidos dentro de un producto:
SELECT SalesOrderID,
MAX(UnitPrice * OrderQty) as MaxAmount
FROM Sales.SalesOrderDetail GROUP BY SalesOrderID
cláusula HAVING
la cláusula HAVING de SQL se utiliza para filtrar grupos de acuerdo con los resultados de las funciones aggregate. Esto permite resolver problemas como seleccionar todos los pedidos que tienen más de dos líneas de detalle de pedido.,
ese ejemplo se parece a
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING Count(SalesOrderDetailID) > 2
Si queremos encontrar todos los pedidos superiores a 1 1000 escribiríamos
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(UnitPrice * OrderQty) > 1000
ORDER BY TotalPrice DESC
tenga en cuenta que podemos usar el alias TotalPrice en la cláusula ORDER BY, pero luego tener una cláusula tiene que usar la expresión.
para hammer home teniendo, quiero mostrar un último ejemplo. Aquí verá que la instrucción HAVING incluye una función agregada que no está en la lista de selección.,
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING AVG(UnitPrice * OrderQty) > 500
ORDER BY TotalPrice DESC
en la consulta anterior, obtenemos el precio total para pedidos donde el monto promedio de SalesOrderDetail es superior a $500.00.
declaración Final sobre tener
aunque realizan una función similar, hay una distinción clave entre la cláusula WHERE y tener. La cláusula WHERE filtra registros individuales; mientras que la cláusula HAVING filtra los grupos.
para mantenerlo en mi cabeza me gusta pensar en la cláusula WHERE haciendo su trabajo antes de que cualquier agrupación tenga lugar, y luego la cláusula HAVING haciéndose cargo después de que los grupos se forman.,
Ejercicios
Es importante para practicar! Utilice la base de datos de ejemplo para responder estas preguntas.
- HR quiere un informe del número de empleados activos por cargo. ¿Qué SQL usarías?
- Muestra la cantidad mínima, máxima y media pedida para cada producto en SalesOrderDetails.
- enumere todos los títulos de trabajo de los empleados, y el número de empleados donde el número promedio de horas de licencia por enfermedad es menor o igual a cuarenta.
- Para un título de trabajo devuelto en el #3 anterior, ¿el recuento es el mismo para el resultado de la respuesta #1 del título de trabajo correspondiente?
las Respuestas están Aquí!,
Leave a Reply