Filtrando agregados
esquema de la base de datos: esquema.txt
datos: datos.txt
Necesitas ejecutar en HeidiSQL el siguiente comando:
SET @@SESSION.sql_mode=CONCAT(@@SQL_MODE,',ONLY_FULL_GROUP_BY,ANSI');
Esto solo afecta a la sesión abierta en HeidiSQL; cerrada esta, cuando vuelvas a conectarte al servidor, tendrás que ejecutarlo otra vez si quieres volver al modo SQL estándar.
Para hacerlo permanente, necesitas actualizar el fichero de configuración de MariaDB.
Ya sabemos cómo hacer cálculos de agregación. Vayamos ahora un poco más allá.
select p.usuario, p.fecha, l.numpedido, l.linea, l.articulo, l.pagado, l.cantidad
from pedido p, linped l
where p.numpedido=l.numpedido
Acabamos de obtener toda la información de pedidos, mostrando quién pide, cuándo lo pidió y qué pidió; finalmente, qué pagó por cada unidad y la cantidad de unidades de cada artículo pedido.
De toda esta información en bruto podemos sacar datos, información agregada como "cuánto se ha gastado cada usuario en nuestra tienda".
select p.usuario,sum(l.pagado*l.cantidad) cobrado
from pedido p, linped l
where p.numpedido=l.numpedido
group by p.usuario
Y supongamos que queremos afinar más, que solo nos interesa qué usuarios se han gastado más de 1000 €. Así, a bote pronto, se nos ocurre poner una condición más en el where.
select p.usuario,sum(l.pagado*l.cantidad) cobrado
from pedido p, linped l
where p.numpedido=l.numpedido AND cobrado>1000
group by p.usuario
/* Error de SQL (1054) Unknown column 'cobrado' in where clause */
¡Upss! No ha funcionado, a ver si…
select p.usuario,sum(l.pagado*l.cantidad) cobrado
from pedido p, linped l
where p.numpedido=l.numpedido AND sum(l.pagado*l.cantidad)>1000
group by p.usuario
No. Tampoco. ¿Qué pasa?
Pues que nos falta introducir algo más en la consulta, algo asociado a group by.
/* Error de SQL (1111) Invalid use of group function */
Recordemos que where trabaja fila a fila, sobre los valores que se encuentran en las filas de las tablas que hemos puesto en el from. Examinemos los pedidos de un usuario concreto.
select l.numpedido,l.linea,p.usuario,l.pagado,l.cantidad, l.pagado*l.cantidad cobrado
from pedido p, linped l
where p.numpedido=l.numpedido
AND p.usuario='acm@colegas.com'
El pedido 5 tiene 5 líneas de pedido, y el 34 tiene 3; en total 8 filas de resultado de la consulta.
Sin embargo, solo la línea 2 cumple con que "pagado" por "cantidad" es superior a 1000 €.
select l.numpedido,l.linea,p.usuario,l.pagado,l.cantidad, l.pagado*l.cantidad cobrado
from pedido p, linped l
where p.numpedido=l.numpedido AND p.numpedido=5
AND l.pagado*l.cantidad>1000
Vamos a decirlo así: el group by se ejecuta después del where. Si añadimos la agregación a la primera consulta obtenemos el total cobrado en cada pedido a ese usuario.
select l.numpedido,p.usuario,
sum(l.pagado*l.cantidad) cobrado
from pedido p, linped l
where p.numpedido=l.numpedido AND p.usuario='acm@colegas.com'
group by l.numpedido,p.usuario;
Sin embargo, añadiendo la condición a where, lo que obtenemos es "total cobrado en cada pedido de ese usuario, ignorando las líneas con un importe cobrado menor o igual que 1000".
select l.numpedido,p.usuario,
sum(l.pagado*l.cantidad) cobrado
from pedido p, linped l
where p.numpedido=l.numpedido AND p.usuario='acm@colegas.com'
AND l.pagado*l.cantidad>1000
group by l.numpedido,p.usuario;
Un poco raro, sí. Veamos qué hace having.
select l.numpedido,p.usuario,
sum(l.pagado*l.cantidad) cobrado
from pedido p, linped l
where p.numpedido=l.numpedido AND p.usuario='acm@colegas.com'
group by l.numpedido,p.usuario
having sum(l.pagado*l.cantidad)>1000;
Ahora sí, primero calcula los totales por pedido, sin ignorar filas, y después filtra para quedarse solo con el pedido 5. O sea, debemos pensar que se sigue este orden de ejecución:
from
where
group by
having
select
No hay having sin group by, las condiciones de having no se pueden poner en where. En having se pregunta por cálculos de agregación (suma de valores de varias filas, promedio, máximo, etc.). En where se puede preguntar por cálculos aritméticos entre valores de fila y constantes.
Después vienen todas las variaciones posibles: la condición del having no tiene porqué coincidir con lo puesto en el select, podemos preguntar por lo que queramos. Por ejemplo, qué le hemos cobrado a los clientes que nos han hecho 2 o más pedidos.
select p.usuario,sum(l.pagado*l.cantidad) cobrado
from pedido p, linped l
where p.numpedido=l.numpedido
group by p.usuario
having count(distinct p.numpedido)>=2
¿No te lo crees? Ejecutamos esta variación de una consulta anterior.
select p.usuario,sum(l.pagado*l.cantidad) cobrado, count(distinct p.numpedido) pedidos
from pedido p, linped l
where p.numpedido=l.numpedido
group by p.usuario
order by pedidos desc
Y efectivamente, son esos dos clientes, los primeros en la tabla, los únicos que tienen 2 o más pedidos. Vale, que sigues sin verlo. Solo para esos dos usuarios, mostramos toda la información almacenada en pedido y linped.
select p.usuario, p.fecha, l.numpedido, l.linea, l.articulo, l.pagado, l.cantidad
from pedido p, linped l
where p.numpedido=l.numpedido
and p.usuario in ('mraj@colegas.com', 'acm@colegas.com')
order by p.usuario,l.numpedido,l.linea
Como filtro adicional hemos usado la función IN, la pertenencia a lista, esto es, que el usuario sea uno de la lista. De hecho, es equivalente a una disyunción típica, pero escrito de forma más compacta.
p.usuario = 'mraj@colegas.com' OR p.usuario = 'acm@colegas.com'
Examinando el resultado de la consulta, estos son los datos de pedidos de esos dos usuarios, en bruto, sin agregar, tal cual sale de una consulta normal. Todos los artículos pedidos por ellos. Pero sí podemos ver que cada usuario tiene dos pedidos distintos, el 5 y el 34 para "acm", y el 14 y el 36 para "mraj". Pues eso, count(distinct p.numpedido) hace lo que intuimos, contar números de pedido distintos.
Recuerda, having solo tiene sentido usarlo si acompaña a group by. Y sí, el orden es inamovible: primero from, después where, más tarde group by y, por último, having.