Tutorial SQL #6: Agrupaciones y funciones de agregación

26/11/2021Artículo original

Terminamos con nuestro tutorial sobre los fundamentos del lenguaje de consultas SQL. Recuerda: si quieres aprender SQL en serio con todo el apoyo de un tutor especialista, nuestro curso online de fundamentos de SQL es tu mejor opción para aprender de forma rápida y eficaz.

Hasta ahora hemos estudiado las consultas simples, las consultas multi-tabla, los diferentes tipos de JOIN en las consultas multi-tabla y las operaciones con conjuntos, (aparte de poner en marcha el entorno de pruebas y conocer la base de datos que vamos a utilizar).

En esta ocasión vamos a estudiar cómo generar resultados de consultas agrupados y con algunas operaciones de agregación aplicadas.

Funciones de agregación

Las funciones de agregación en SQL nos permiten efectuar operaciones sobre un conjunto de resultados, pero devolviendo un único valor agregado para todos ellos. Es decir, nos permiten obtener medias, máximos, etc… sobre un conjunto de valores.

Las funciones de agregación básicas que soportan todos los gestores de datos son las siguientes:

• COUNT: devuelve el número total de filas seleccionadas por la consulta.
• MIN: devuelve el valor mínimo del campo que especifiquemos.
• MAX: devuelve el valor máximo del campo que especifiquemos.
• SUM: suma los valores del campo que especifiquemos. Sólo se puede utilizar en columnas numéricas.
• AVG: devuelve el valor promedio del campo que especifiquemos. Sólo se puede utilizar en columnas numéricas.

Todas estas funciones se aplican a una sola columna, que especificaremos entre paréntesis, excepto la función COUNT, que se puede aplicar a una columna o indicar un “*”. La diferencia entre poner el nombre de una columna o un “*”, es que en el primer caso no cuenta los valores nulos para dicha columna, y en el segundo si.

Nota: Si esta serie de artículos te está pareciendo interesante, entonces ni te imaginas lo que puedes aprender con este curso de fundamentos de SQL.

Así, por ejemplo, si queremos obtener algunos datos agregados de la tabla de pedidos de la base de datos de ejemplo Northwind, podemos escribir una consulta simple como la siguiente:

SELECT COUNT(*) AS TotalFilas, COUNT(ShipRegion) AS FilasNoNulas, MIN(ShippedDate) AS FechaMin, MAX(ShippedDate) AS FechaMax, SUM(Freight) AS PesoTotal, AVG(Freight) PesoPromedioFROM Orders

y obtendríamos el siguiente resultado en el entorno de pruebas:

De esta manera sabremos que existen en total 830 pedidos en la base de datos, 323 registros que tienen asignada una zona de entrega, la fecha del pedido más antiguo (el 10 de julio de 1996), la fecha del pedido más reciente (el 6 de mayo de 1998 ¡los datos de ejemplo son muy antiguos!), el total de peso enviado entre todos los pedidos (64.942,69 Kg o sea, más de 64 toneladas) y el peso promedio del los envíos (78,2442Kg). No está mal para una consulta tan simple.

Como podemos observar del resultado de la consulta anterior, las funciones de agregación devuelven una sola fila, salvo que vayan unidas a la cláusula GROUP BY, que veremos a continuación.

Agrupando resultados

La cláusula GROUP BY unida a un SELECT permite agrupar filas según las columnas que se indiquen como parámetros, y se suele utilizar en conjunto con las funciones de agrupación, para obtener datos resumidos y agrupados por las columnas que se necesiten.

Hemos visto en el ejemplo anterior que obteníamos sólo una fila con los datos indicados correspondientes a toda la tabla. Ahora vamos a ver con otro ejemplo cómo obtener datos correspondientes a diversos grupos de filas, concretamente agrupados por cada empleado:

SELECT EmployeeID, COUNT(*) AS TotalPedidos, COUNT(ShipRegion) AS FilasNoNulas, MIN(ShippedDate) AS FechaMin, MAX(ShippedDate) AS FechaMax, SUM(Freight) PesoTotal, AVG(Freight) PesoPromedioFROM OrdersGROUP BY EmployeeID

En este caso obtenemos los mismos datos pero agrupándolos por empleado, de modo que para cada empleado de la base de datos sabemos cuántos pedidos ha realizado, cuándo fue el primero y el último, etc…:

De hecho nos resultaría muy fácil cruzarla con la tabla de empleados, usando lo aprendido sobre consultas multi-tabla, y que se devolvieran los mismos resultados con el nombre y los apellidos de cada empleado:

En este caso fíjate en cómo hemos usado la expresión Employees.FirstName + ‘ ‘ + Employees.LastName como parámetro en GROUP BY para que nos agrupe por un campo compuesto (en SQL Server no podemos usar alias de campos para las agrupaciones). De esta forma tenemos casi un informe preparado con una simple consulta de agregación.

La cláusula GROUP BY se puede utilizar con más de un campo al mismo tiempo. Si indicamos más de un campo como parámetro nos devolverá la información agrupada por los registros que tengan el mismo valor en los campos indicados.

Por ejemplo, si queremos conocer la cantidad de pedidos que cada empleado ha enviado a través de cada transportista, podemos escribir una consulta como la siguiente:

SELECT Employees.FirstName + ' ' + Employees.LastName AS Empleado,Shippers.CompanyName AS Transportista, COUNT(Orders.OrderID)AS NumPedidosFROM Orders INNER JOIN Shippers ON Orders.ShipVia = Shippers.ShipperIDINNER JOIN Employees ON Orders.EmployeeID=Employees.EmployeeIDGROUP BY Employees.FirstName + ' ' + Employees.LastName, Shippers.CompanyName

Con el siguiente resultado:

Así, sabremos que Andrew Fuller envió 25 pedidos con Federal Shipping, y 35 con Federal Express.

El utilizar la cláusula GROUP BY no garantiza que los datos se devuelvan ordenados. Suele ser una práctica recomendable incluir una cláusula ORDER BY por las mismas columnas que utilicemos en GROUP BY, especificando el orden que nos interese. Por ejemplo, en el caso anterior

Existe una cláusula especial, parecida a la WHERE que ya conocemos que nos permite especificar las condiciones de filtro para los diferentes grupos de filas que devuelven estas consultas agregadas. Esta cláusula es HAVING.

Por ejemplo, si queremos repetir la consulta de pedidos por empleado de hace un rato, pero obteniendo solamente aquellos que hayan enviado más de 5.000 Kg de producto, y ordenados por el nombre del empleado, la consulta sería muy sencilla usando HAVING y ORDER BY:

SELECT Employees.FirstName + ' ' + Employees.LastName AS Empleado, COUNT(*) AS TotalPedidos, COUNT(ShipRegion) AS FilasNoNulas, MIN(ShippedDate) AS FechaMin, MAX(ShippedDate) AS FechaMax, SUM(Freight) PesoTotal, AVG(Freight) PesoPromedioFROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeIDGROUP BY Employees.FirstName + ' ' + Employees.LastNameHAVING SUM(Freight) > 5000ORDER BY Employees.FirstName + ' ' + Employees.LastName ASC

Ahora obtenemos los resultados agrupados por empleado también, pero solo aquellos que cumplan la condición indicada (o condiciones indicadas, pues se pueden combinar). Antes nos salían 9 empleados, y ahora solo 6 pues hay 3 cuyos envíos totales son muy pequeños: