Menú de navegaciónMenú
Categorías

La mejor forma de Aprender Programación online y en español www.campusmvp.es

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

SQL-Funciones-Agregacion

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.

Las funciones anteriores son las básicas en SQL, pero cada sistema gestor de bases de datos relacionales ofrece su propio conjunto, más amplio, con otras funciones de agregación particulares. Puedes consultar las que ofrecen SQL Server, Oracle o MySQL.

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.

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) PesoPromedio
FROM Orders

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

Valores-Agregados-SQL-Ej1

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) PesoPromedio
FROM Orders
GROUP 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...:

Valores-Agregados-SQL-Ej2

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:

Valores-Agregados-SQL-Ej3

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.

Importante: Es muy importante tener en cuenta que cuando utilizamos la cláusula GROUP BY, los únicos campos que podemos incluir en el SELECT sin que estén dentro de una función de agregación, son los que vayan especificados en el GROUP BY..

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 NumPedidos
FROM Orders INNER JOIN Shippers ON Orders.ShipVia = Shippers.ShipperID
INNER JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID
GROUP BY Employees.FirstName + ' ' + Employees.LastName, Shippers.CompanyName

Con el siguiente resultado:

Valores-Agregados-SQL-Ej4

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.

HAVING es muy similar a la cláusula WHERE, pero en vez de afectar a las filas de la tabla, afecta a los grupos obtenidos.

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) PesoPromedio
FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
GROUP BY Employees.FirstName + ' ' + Employees.LastName
HAVING SUM(Freight) > 5000
ORDER 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:

Valores-Agregados-SQL-Ej5

 

José M. Alarcón Aguín Fundador de campusMVP, es ingeniero industrial y especialista en consultoría de empresa. Ha escrito diversos libros, habiendo publicado hasta la fecha cientos de artículos sobre informática e ingeniería en publicaciones especializadas. Microsoft lo ha reconocido como MVP (Most Valuable Professional) en desarrollo web desde el año 2004 hasta la actualidad. Puedes seguirlo en Twitter en @jm_alarcon o leer sus blog técnico o personal. Ver todos los posts de José M. Alarcón Aguín
Archivado en: Acceso a Datos

Boletín campusMVP.es

Solo cosas útiles. Una vez al mes.

🚀 Únete a miles de desarrolladores

DATE DE ALTA

x No me interesa | x Ya soy suscriptor

La mejor formación online para desarrolladores como tú

Comentarios (9) -

hola quisiera saber si me pueden ayudar lo que pasa es que tengo una columna de precios y ocupo hacer un select precio from tabla pero en otra columna y en el mismo query sacar el max de todos esos precios , alguien que me ayude por favor

Por favor, donde podria encontrar toda la informacion de las tablas para hacer los test correspondientes. muchas gracias por su respuesta

tengo otras tablas para realizar test que aplican a este tutorial y otros mas que encontre

Hola:

Hay alguna consulta o reporte para saber cuantas operaciones y de que tipo realiza SQL en un periodo de tiempo?

campusMVP
campusMVP

Hola Luis:

No hay nada estándar en el lenguaje SQL para esa información, cada gestor de bases de datos tiene sus propios sistemas.

Por ejemplo, en SQL Server se guardan en tablas del sistema estadísticas de un montón de cosas que se están ejecutando en el motor, de modo que puedes lanzar consultas sobre ellas para averiguar todo tipo de cuestiones: msdn.microsoft.com/es-es/library/ms188068.aspx

Saludos.

Buenas tardes, estoy haciendo una consulta, pero no entiendo porqué me cambian los valores, me explico:
cuando ejecuto la siguiente consulta me arroja lo valores máximos
SELECT DISTINCT ON (uplcodigo) uplcodigo, MAX(valor)
FROM gis.h_iboca
GROUP BY uplcodigo

este es el resultado, revisé y si es el mayor valor
“UPZ1”;40
“UPZ10”;40
“UPZ100”;40
“UPZ101”;40
“UPZ102”;40
“UPZ103”;40
“UPZ104”;40

pero cuando le agrego un campo, me cambia el resultado
SELECT DISTINCT ON (uplcodigo) uplcodigo, MAX(valor), var
FROM gis.h_iboca
GROUP BY uplcodigo, var

Este es el resultado, no es consistenete
“UPZ1″;”O3”;30
“UPZ10″;”PM2_5”;20
“UPZ100″;”O3”;20
“UPZ101″;”O3”;20
“UPZ102″;”SO2”;30
“UPZ103″;”CO”;30
“UPZ104″;”SO2”;30

Me debería entregar el registro de la variable “var” asociado al valor máximo y no lo está haciendo. También , cuando intento hacer un join me genera error de sintaxis. cabe anotar que soy nueva en esto

SELECT DISTINCT ON (h_iboca.uplcodigo) loc_upz_wgs84.gid, loc_upz_wgs84.geom, h_iboca.uplcodigo, h_iboca.uplnombre, h_iboca.var, h_iboca.valor
FROM gis.h_iboca, loc_upz_wgs84
WHERE h_iboca.var= h_iboca.var
GROUP BY h_iboca.uplcodigo, loc_upz_wgs84.gid, loc_upz_wgs84.geom, h_iboca.uplcodigo, h_iboca.uplnombre, h_iboca.var, h_iboca.valor
JOIN
ON loc_upz_wgs84.uplcodigo = h_iboca.uplcodigo

CRISTOPHER GOMEZ
CRISTOPHER GOMEZ

Hola soy un NOB en esto y tengo una duda

En esta parte de la sentencia que nos filtra los valores mayores a 500

HAVING SUM(Freight) > 5000

¿Solo se usan números como ese '5000'? ó
¿Como seria si quiero los que son mayores al promedio pero sin conocer ese valor promedio?
algo así como

HAVING Freight > AVG(Freight)

Saludos y muchas gracias

Para eso es necesario que el promedio lo saques en una subconsulta.

Como harías para poder obtener los valores maximos de una tabla y luego sumarlos

Pingbacks and trackbacks (1)+

No se aceptan más comentarios