Hay ocasiones en las que nos es útil aplicar algunas funciones de agregación únicamente sobre ciertos registros.
Si bien lo podemos hacer filtrando los datos, esto no es muy conveniente porque tendríamos que repetir la consulta tantas veces como datos nos interese consultar. Ej.
-- Seleccionamos distintas columnas
-- de la tabla publicaciones
-- con las que queremos trabajar.
SELECT usuario_id, nivel_id, vistas, votos FROM publicaciones;
-- Si queremos obtener la sumatoria
-- de vistas y votos por usuario
-- tenemos que especificar
-- el usuario.
SELECT usuario_id, nivel_id, vistas, votos
FROM publicaciones
WHERE usuario_id = 4;
-- Aplicamos las funciones
-- que nos van a ayudar a obtener
-- los datos que necesitemos.
-- En este caso quité nivel_id
-- porque no es necesaria
-- para esta consulta.
SELECT usuario_id, SUM(vistas), SUM(votos)
FROM publicaciones
WHERE usuario_id = 4;
-- En el caso de querer agrupar
-- por otra columna repetimos
-- el procedimiento.
-- Si queremos agrupar por otro
-- usuario o nivel, cambiamos
-- el valor utilizado en la comparación.
SELECT nivel_id, SUM(vistas), SUM(votos)
FROM publicaciones
WHERE nivel_id = 1
Aunque es posible obtener los valores filtrando datos es posible hacerlo de una mejor manera.
GROUP BY
Con GROUP BY podemos elegir la columna con la cuál queremos agrupar diferentes registros.
-- En el caso de querer la sumatoria
-- de vistas y votos por la columna usuario_id
-- agrupamos los registros por el
-- valor de esa columna.
-- Incluso toma en cuenta los registros
-- cuyo valor es NULL
SELECT usuario_id, SUM(vistas), SUM(votos)
FROM publicaciones
GROUP BY usuario_id;
-- Podemos hacer lo mismo
-- si nos interesa agrupar
-- por la columna nivel_id
SELECT nivel_id, SUM(vistas), SUM(votos)
FROM publicaciones
GROUP BY nivel_id;
Nota:
Al utilizar GROUP BY es conveniente seleccionar únicamente las columnas que vamos a utilizar para procesar datos – para agrupar y para aplicar alguna función sobre ellos -. Aunque es posible elegir otras columnas, no se puede determinar el orden en el que va a aparecer el contenido – como esos valores representan un atributo de cada registro es poco probable que puedan ser útiles para representar el valor de un grupo de registros – .
-- Incluimos la columna id y título pero
-- si estamos creando distintos grupos
-- con los valores de la columna usuario_id
-- las columnas id y título no son
-- representativas del grupo
-- generado porque el id
-- es el identificador de cada registro
-- y el título es el título de cada publicación.
SELECT id, titulo, usuario_id, SUM(vistas), SUM(votos)
FROM publicaciones
GROUP BY usuario_id;
Solo se visualiza un registro por valor distinto que exista en la(s) columna(s) elegida(s) en GROUP BY – en este caso usuario_id – , porque están agrupados por esa columna.
Filtrar, ordenar
¿Qué pasa si queremos filtrar los datos que queremos incluir en el grupo?
Por ejemplo que las publicaciones sean del año 2012 y no nos interesa crear un grupo donde el valor en usuario_id sea NULL.
-- Podemos filtrar los registros que van
-- a ser tomados en cuenta para
-- crear los grupos al utilizar WHERE
-- como lo hemos hecho.
SELECT usuario_id, SUM(vistas), SUM(votos)
FROM publicaciones
WHERE YEAR(creado) = 2012 AND usuario_id IS NOT NULL
GROUP BY usuario_id;
Una vez que tenemos los resultados podemos utilizar ORDER BY para ordenarlos, como lo hemos hecho.
-- Utilicé alias de columna para las
-- columnas donde se aplica la sumatoria.
-- De esta manera me es más fácil
-- hacer referencia a la columna
-- al momento de ordenar los resultados
SELECT usuario_id, SUM(vistas) AS 'sumatoria_vistas', SUM(votos) AS 'sumatoria_votos'
FROM publicaciones
WHERE YEAR(creado) = 2012 AND usuario_id IS NOT NULL
GROUP BY usuario_id
ORDER BY sumatoria_vistas ASC;
Entonces con WHERE podemos filtrar los registros para que sean tomados en cuenta aquellos que cumplan con la(s) condición(es) y con ORDER BY podemos ordenar los registros con los valores que se obtuvieron después de agrupar y aplicar las funciones.
Filtrar por datos calculados
Hasta ahora hemos agrupado, calculado, filtrado los datos que van a ser tomados en cuenta para crear los grupos y ordenado los resultados de esta consulta pero ¿qué pasa si queremos mostrar únicamente los datos que cumplen con una condición?
La primer reacción es “incluirlos como otras condiciones en WHERE y utilizar AND / OR para agrupar” pero esto no nos va a servir.
-- Aunque parecería que con WHERE
-- podemos filtrar los registros después
-- de que fueron agrupados y que las
-- funciones fueron aplicadas no es el caso.
SELECT usuario_id, SUM(vistas) AS 'sumatoria_vistas', SUM(votos) AS 'sumatoria_votos', MIN(vistas)
FROM publicaciones
WHERE sumatoria_vistas > 10 AND sumatoria_votos > 10
GROUP BY usuario_id;
-- Unknown column 'sumatoria_vistas' in 'where clause'Para lograr este objetivo tenemos que utilizar HAVING.
HAVING funciona como WHERE – que nos permite filtrar resultados – pero el filtro se aplica a los valores obtenidos después de agrupar los datos.
HAVING y WHERE se pueden utilizar en la misma consulta para que se filtren los resultados que se quieren incluir en los grupos y, una vez procesados los datos se muestren únicamente los que cumplen con las condiciones especificadas en HAVING.
-- Podemos utilizar HAVING y WHERE
-- en la misma sentencia.
-- WHERE para filtrar los registros
-- ANTES de ser agrupados
-- HAVING para filtrar los registros
-- DESPUÉS de ser agrupados.
SELECT usuario_id, SUM(vistas) AS 'sumatoria_vistas', SUM(votos) AS 'sumatoria_votos'
FROM publicaciones
WHERE usuario_id IS NOT NULL
GROUP BY usuario_id
HAVING sumatoria_vistas > 10 AND sumatoria_votos > 10;
-- Si queremos, también podemos ordenar
-- los resultados obtenidos con ORDER BY
SELECT usuario_id, SUM(vistas) AS 'sumatoria_vistas', SUM(votos) AS 'sumatoria_votos'
FROM publicaciones
WHERE usuario_id IS NOT NULL
GROUP BY usuario_id
HAVING sumatoria_vistas > 10 AND sumatoria_votos > 10
ORDER BY sumatoria_votos DESC;
Obtener los datos generales del grupo
Finalmente ¿qué pasa si queremos obtener los datos generales de los valores obtenidos en los grupos? es decir, el resultado de las funciones utilizadas en cada una de las columnas, tomando en cuenta todos los valores.
Para eso podemos utilizar WITH ROLLUP
-- Al utilizar WITH ROLLUP
-- vamos a obtener un
-- registro adicional con el resultado
-- de la(s) funcion(es) de agregación
-- que utilicemos sobre las columnas.
SELECT usuario_id, SUM(vistas) AS 'sumatoria_vistas', SUM(votos) AS 'sumatoria_votos'
FROM publicaciones
WHERE usuario_id IS NOT NULL
GROUP BY usuario_id WITH ROLLUP;
ROLLUP tiene algunos inconvenientes como el que no se puede utilizar con ORDER BY.
Si queremos ordenar los registros podemos utilizar ASC o DESC en la(s) columna(s) que utilicemos para agrupar los registros.
-- Al utilizar WITH ROLLUP
-- no se puede utilizar ORDER BY
-- Si queremos ordenar los registros
-- tenemos que establecer el orden
-- después de la columna en GROUP BY.
-- Independientemente del orden elegido
-- el renglón adicional que se muestra
-- al utilizar WITH ROLLUP se va a encontrar
-- al final de los registros.
SELECT usuario_id, SUM(vistas) AS 'sumatoria_vistas', SUM(votos) AS 'sumatoria_votos'
FROM publicaciones
WHERE usuario_id IS NOT NULL
GROUP BY usuario_id DESC WITH ROLLUP;
Otro inconveniente es que, aunque filtremos registros utilizando HAVING estos son tomados en cuenta al realizar los cálculos.
-- Al utilizar HAVING con WITH ROLLUP
-- va a ocultar los renglones que no
-- cumplen con la condición pero
-- van a ser tomados en cuenta para
-- el cálculo de WITH ROLLUP.
SELECT usuario_id, SUM(vistas) AS 'sumatoria_vistas', SUM(votos) AS 'sumatoria_votos'
FROM publicaciones
WHERE usuario_id IS NOT NULL
GROUP BY usuario_id WITH ROLLUP
HAVING sumatoria_vistas > 60;
Referencia
Finalmente una referencia donde se utiliza GROUP BY y distintas funciones de agregación sobre la columna vistas de la tabla publicaciones.
-- Aquí preferí enfocar las funciones de agregación
-- en una sola columna: vistas
-- De esta manera podemos verificar que WITH ROLLUP
-- aplica las funciones a todos los registros.
-- IFNULL: No lo hemos visto. Si el valor de un registro en la columna especificada es NULL
-- lo sustituye por el valor que asignemos.
-- COUNT: Nos interesa saber cuántos registros están siendo utilizados
-- en el grupo
-- SUM, AVG, MAX, MIN - Funciones de agregación que vimos.
SELECT IFNULL(usuario_id, "Resultados Vistas") AS 'usuario', COUNT(id) AS 'registros',
SUM(vistas) AS 'sumatoria', AVG(vistas) 'promedio',
MAX(vistas) 'máximo', MIN(vistas) 'minimo'
FROM publicaciones
WHERE usuario_id IS NOT NULL
GROUP BY usuario_id DESC WITH ROLLUP;Finalmente
Agrupar registros – y aplicar funciones sobre cada grupo – es una tarea difícil. Una vez conociendo lo que se puede hacer con GROUP BY hay que probar y verificar con nuestros datos.