Funciones agregadoras y agrupación de datos con MySQL

Clase 21 de 24 • Curso de Fundamentos de Bases de Datos

Resumen

Aprender a utilizar funciones agregadoras y group by en MySQL permite transformar datos crudos en valiosa información de negocio. Conocer cómo aplicar funciones como COUNT, SUM y AVG, y combinar todo ello con sentencias WHERE y HAVING, brinda claridad sobre datos específicos ideales para la toma de decisiones comerciales estratégicas.

¿Qué son y para qué sirven las funciones agregadoras en MySQL?

Las funciones agregadoras, como su nombre lo indica, combinan múltiples filas de datos y producen un resultado resumido. Algunas funciones frecuentes son:

COUNT: cuenta las filas que cumplen con cierta condición. Por ejemplo, select count(*) from products where price < 500; te dará la cantidad de productos con precios por debajo de 500 pesos.
SUM: calcula la suma total de valores en una columna numérica específica. Por ejemplo, sumando el stock disponible se obtiene la cantidad total de artículos disponibles originalmente.
AVG: muestra el promedio de valores en columnas numéricas, como calcular el precio promedio de productos.

Estas funciones son esenciales para extraer información práctica y comprensible de grandes cantidades de datos.

¿Cómo sacar provecho del ´case´ y ´group by´?

La cláusula GROUP BY permite agrupar filas según valores comunes en una columna específica, mientras que CASE permite definir resultados según condiciones concretas.

¿Cómo categorizar usando ´case´?

La estructura básica del comando CASE es así:

SELECT email,
       CASE
         WHEN email LIKE '%@gmail.com' THEN 'Gmail'
         WHEN email LIKE '%@hotmail.com' THEN 'Hotmail'
         ELSE 'Otro proveedor'
       END AS proveedor
FROM clients;

Este código evalúa cada dirección de correo y asigna una categoría basada en su proveedor.

¿Qué sucede al combinar ´case´ con ´group by´?

Si deseas contar la cantidad de clientes según el proveedor de su correo electrónico, utiliza:

SELECT CASE
         WHEN email LIKE '%@gmail.com' THEN 'Gmail'
         WHEN email LIKE '%@hotmail.com' THEN 'Hotmail'
         ELSE 'Otro proveedor'
       END AS proveedor,
       COUNT(*) AS total_clients
FROM clients
GROUP BY proveedor;

La combinación genera información organizada que muestra claramente la cantidad de cuentas de cada proveedor de correo existentes en la base de clientes.

¿Cómo optimizar búsquedas usando ´where´y ´having´?

Usar conjuntamente WHERE, GROUP BY y HAVING permite disfrutar aún más del poder de MySQL al ordenar y filtrar conjuntos extensos de datos:

WHERE filtra filas antes del proceso de agrupamiento.
HAVING hace lo mismo, pero posterior al agrupamiento, funcionando específicamente sobre resultados agrupados.

¿Cómo se usan de forma conjunta?

Por ejemplo, si se quiere obtener solamente los proveedores con menos de cien clientes, la consulta sería:

SELECT CASE
         WHEN email LIKE '%@gmail.com' THEN 'Gmail'
         WHEN email LIKE '%@hotmail.com' THEN 'Hotmail'
         ELSE 'Otro proveedor'
       END AS proveedor,
       COUNT(*) AS total_clients
FROM clients
GROUP BY proveedor
HAVING total_clients < 100;

Esta consulta solo devolverá los proveedores de correo electrónico con menos de cien registros.

Invitamos a todos los interesados en mejorar sus habilidades en SQL a compartir dudas y ejercicios prácticos adicionales en los comentarios.

gemma clavero del moral

student•

La diferencia entre HAVING y WHERE en SQL radica en el momento en que se aplican:

WHERE se utiliza para filtrar filas antes de cualquier agrupamiento. Se aplica a las filas individuales de la tabla, es decir, antes de que se realice cualquier cálculo de agregaciones.
HAVING, en cambio, se utiliza para filtrar grupos de resultados después de que se han realizado las agregaciones. Se aplica a los resultados de la función GROUP BY.

En resumen, usa WHERE para condiciones en filas individuales y HAVING para condiciones en resultados agregados.

Sergio de Jesús Huesca Nieva

student•

Para hacerlo mas dinamico, se podría buscar el string posterior al arroba y ya hacer el conteo, así eliminamos el ruido de otro proveedor de correo:

select

SUBSTRING_INDEX(email, '@', -1) AS proveedor,

COUNT(*) AS cantidad

from

clients

group by

proveedor

;

Alan Alejandro Villegas Espinoza

student•

Muy util bro, agrege un ORDER BY cantidad DESC ; para que me muestre en orden descendiente la cantidad de emails

select SUBSTRING_INDEX(email, '@', -1) AS proveedor,

COUNT(*) AS cantidad from clients

group by proveedor

ORDER BY cantidad DESC ;

y con ASC para ver los que menos cantidad tienen:

Diego Orta

student•

Siguiendo los pasos de Sergio y Alan, me surgió la curiosidad de dejar solo el nombre del proveedor como el ejercicio que venimos haciendo en clases, y descubrí que también se puede aplicar como una cadena dentro de otra cadena.

SELECT SUBSTRING_INDEX (SUBSTRING_INDEX(email,'@',-1),'.',1) AS proveedor,

COUNT(*) AS cantidad

FROM clients

GROUP BY proveedor

ORDER BY cantidad DESC;

Enrique Alexis Lopez Araujo

student•

Las funciones de ventana en SQL son fundamentales para realizar cálculos en conjuntos de filas que están relacionadas entre sí. Aquí hay un resumen con ejemplos de las más importantes:

ROW_NUMBER(): Asigna un número único a cada fila de un conjunto de resultados.

SELECT email, ROW_NUMBER() OVER (ORDER BY email) AS row_num FROM clients;

RANK(): Similar a ROW_NUMBER(), pero permite que filas con valores iguales reciban el mismo rango.
```
SELECT email, RANK() OVER (ORDER BY total_purchases DESC) AS rank FROM clients;
```

DENSE_RANK(): Igual que RANK(), pero no deja huecos en los números.

SELECT email, DENSE_RANK() OVER (ORDER BY total_purchases DESC) AS dense_rank FROM clients;

SUM(): Calcula la suma acumulativa.

SELECT email, SUM(amount) OVER (ORDER BY transaction_date) AS running_total FROM transactions;

Estas funciones permiten realizar análisis complejos y obtener información valiosa de los datos sin necesidad de subconsultas complicadas, facilitando la toma de decisiones empresariales.

Enrique Alexis Lopez Araujo

student•

Al usar funciones como COUNT, SUM y AVG, es importante evitar los siguientes errores comunes:

No usar cláusulas WHERE adecuadas: Esto puede llevar a contar o sumar registros que no sean relevantes, generando resultados erróneos.
No agrupar correctamente: Si se omite la cláusula GROUP BY, se perderá la capacidad de segmentar datos, llevando a resultados globales en lugar de específicos.
Ignorar valores NULL: Las funciones de agregación pueden ignorar estos valores, lo cual puede distorsionar los resultados. Es recomendable manejar adecuadamente estos casos.
No validar la lógica del negocio: Asegúrate de que los criterios de filtrado y agrupación reflejen correctamente los requerimientos del análisis de datos.

Estos aspectos son cruciales para obtener información precisa y útil.

José Antonio De La Paz Fonseca

student•

El IF en SQL se utiliza para ejecutar condiciones dentro de una consulta. Permite evaluar una expresión o condición y retornar resultados diferentes basados en si esa condición es verdadera o falsa. Por ejemplo, puedes usar IF para clasificar datos, como determinar si un email pertenece a Gmail, Hotmail, etc.

Esta función es útil para hacer consultas más dinámicas y personalizadas, lo que te ayuda a obtener información más específica de tus bases de datos.

Irmin Corona 🐯

student•

Use WHERE for individual rows (like age, price, name)
Use HAVING for aggregated results (like COUNT(*), SUM(), AVG())

Juan Diego

student•

Para establecer una condición en SQL que verifique si un valor es distinto de otro, se utiliza la cláusula WHERE junto con el operador <>. Por ejemplo, si deseas seleccionar registros donde el email no sea igual a un valor específico, la consulta sería:

SELECT * FROM clients WHERE email <> 'ejemplo@dominio.com';

En este caso, se seleccionarán todos los registros de la tabla clients donde el email sea diferente a 'ejemplo@dominio.com'.

Natalia Gallego Heras

student•

«Esto no es otra cosa que ruido y mucho cuidado porque, cuando tenemos muchos datos y no los operamos bien, tenemos ruido... y el ruido siempre es muy caro»

Eduardo Johnson A.

student•

esta chevere la clase.

Alan Alejandro Villegas Espinoza

student•

Algunos ejercicios extra para practicar un poco mas:

Muestra el nombre del cliente que empiece con A y ademas sea tenga un email @gmail.com

SELECT name,

CASE

WHEN email LIKE '%@gmail.com' THEN 'Gmail'

WHEN email LIKE '%@hotmail.com' THEN 'Hotmail'

WHEN email LIKE '%@yahoo.com' THEN 'Yahoo'

ELSE 'Otro proveedor'

END AS PROVEEDOR

FROM clients

WHERE name LIKE 'a%' AND email LIKE '%@gmail.com';

Muestra el nombre del cliente que empiece con A y ademas sea tenga un email @hotmail.com

SELECT name,

CASE

WHEN email LIKE '%@gmail.com' THEN 'Gmail'

WHEN email LIKE '%@hotmail.com' THEN 'Hotmail'

WHEN email LIKE '%@yahoo.com' THEN 'Yahoo'

ELSE 'Otro proveedor'

END AS PROVEEDOR

FROM clients

WHERE name LIKE 'a%' AND email LIKE '%@hotmail.com';

Muestra el nombre del cliente que empiece con A y ademas sea tenga un email @yahoo.com

SELECT name,

CASE

WHEN email LIKE '%@gmail.com' THEN 'Gmail'

WHEN email LIKE '%@hotmail.com' THEN 'Hotmail'

WHEN email LIKE '%@yahoo.com' THEN 'Yahoo'

ELSE 'Otro proveedor'

END AS PROVEEDOR

FROM clients

WHERE name LIKE 'a%' AND email LIKE '%@yahoo.com';

Muestra de forma agrupada el PROVEEDOR y el TOTAL_CLIENTES

SELECT

``CASE

`` ``WHEN email LIKE '%@gmail.com' THEN 'Gmail'

`` ``WHEN email LIKE '%@hotmail.com' THEN 'Hotmail'

`` ``WHEN email LIKE '%@yahoo.com' THEN 'Yahoo'

`` ``ELSE 'Otro Proveedor'

``END AS PROVEEDOR,

``COUNT(*) AS TOTAL_CLIENTES

FROM clients

GROUP BY PROVEEDOR

ORDER BY TOTAL_CLIENTES ASC;

Alan Alejandro Villegas Espinoza

student•

Muestra el nombre del cliente que empiece con A y ademas sea tenga un email de Otro proveedor

SELECT name,

CASE

WHEN email LIKE '%@gmail.com' THEN 'Gmail'

WHEN email LIKE '%@hotmail.com' THEN 'Hotmail'

WHEN email LIKE '%@yahoo.com' THEN 'Yahoo'

ELSE 'Otro proveedor'

END AS PROVEEDOR

FROM clients

WHERE name LIKE 'a%'

``AND email NOT LIKE '%@gmail.com'

`` ``AND email NOT LIKE '%@hotmail.com'

`` ``AND email NOT LIKE '%@yahoo.com';

Muestra de forma agrupada el PROVEEDOR y el TOTAL_CLIENTES con el nombre de las personas que empiecen con 'a'

SELECT

``CASE

`` ``WHEN email LIKE '%@gmail.com' THEN 'Gmail con A'

`` ``WHEN email LIKE '%@hotmail.com' THEN 'Hotmail con A'

`` ``WHEN email LIKE '%@yahoo.com' THEN 'Yahoo con A'

`` ``ELSE 'Otro Proveedor con A'

``END AS PROVEEDOR,

``COUNT(*) AS TOTAL_CLIENTES

FROM clients

WHERE name LIKE 'a%'

GROUP BY PROVEEDOR

ORDER BY TOTAL_CLIENTES ASC;

Funciones agregadoras y agrupación de datos con MySQL

Fundamentos de Bases de datos

Fundamentos de SQL y diseño de bases de datos relacionales

Separación de entidades para organizar datos en bases de datos

Qué es SQL y cuándo usarlo en lugar de Excel

Instalación de MySQL en Windows, macOS y Linux

Configuración básica cliente-servidor en MySQL

Introducción a Bases de Datos Relacionales

Conceptos fundamentales de bases de datos: columnas, tuplas y relaciones

Creación de tablas básicas en MySQL con tipos de datos

Claves primarias y validaciones esenciales en tablas MySQL

Normalización y relaciones en bases de datos de tiendas

Creación visual de esquemas de bases de datos con DBML

Manipulación de Datos

Creación de tablas SQL con relaciones y llaves externas

Creación de tabla relacional con llaves foráneas en MySQL

Tipos de tablas en bases de datos relacionales: catálogo, operación, registro y archivo

Modificación de tablas MySQL con ALTER TABLE

Inserción de datos y manejo de claves duplicadas en MySQL

Creación de tablas MySQL realistas con campos únicos y timestamps

Filtrado de datos con WHERE en SQL

Comando UPDATE: modificar registros en tablas SQL

Borrado lógico y físico de datos en SQL

Uso del comando SELECT para consultas básicas en SQL

Agrupación de Datos

Funciones agregadoras y agrupación de datos con MySQL

Cómo llenar tablas usando INSERT INTO SELECT en MySQL

Cómo usar Left Join para conectar tablas relacionales

Consultas complejas con múltiples tablas usando LEFT JOIN en MySQL