MySQL Funciones de agregación: SUMA, CONTAR, AVG y MAX

¿Qué son las funciones agregadas en MySQL?

An función agregada Lee varias filas de una sola columna y las combina en un solo valor. Las funciones de agregación se centran en:

• Realizar cálculos en varias filas
• De una sola columna de una tabla
• Y devolviendo un valor único.

La norma ISO define cinco (5) funciones agregadas, a saber:

1. COUNT
2. SUM
3. AVG
4. MIN
5. MAX

Una regla se aplica a los cinco: Las funciones de agregación ignoran los valores NULL.COUNT(*) es la única excepción, y veremos por qué a continuación.

Por qué utilizar funciones agregadas

Los distintos niveles organizativos tienen diferentes necesidades de información. Los directivos de alto nivel suelen estar interesados ​​en las cifras globales, no en los detalles individuales.

Las funciones agregadas nos permiten producir fácilmente datos resumidos de nuestra base de datos.

Por ejemplo, a partir de nuestra base de datos de myflix, la gerencia puede requerir los siguientes informes:

• Películas menos alquiladas.
• Películas más alquiladas.
• Número promedio de veces que se alquila cada película al mes.

Todos los informes anteriores provienen de funciones de agregación. Analicemos cada uno en detalle.

Función COUNT

La función COUNT devuelve el número total de valores en el campo especificado, tanto para tipos de datos numéricos como no numéricos. Al igual que cualquier función de agregación, COUNT(columna) excluye los valores NULL.

COUNT(*) es una forma especial que devuelve el recuento de todas las filas en una tabla. También cuenta NULLs y duplicados, porque cuenta filas en lugar de valores.

La tabla movierentals contiene los siguientes datos:

Supongamos que queremos obtener el número de veces que se ha alquilado la película con ID 2.

SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;

Ejecutar esto en MySQL Banco de trabajo La consulta contra myflixdb devuelve 3, porque tres filas contienen movie_id 2.

Palabra clave DISTINTA

COUNT responde “cuántos”. La siguiente pregunta suele ser “cuántos”. una experiencia diferente unos”, y para eso sirve DISTINCT.

La palabra clave DISTINCT omite duplicados de nuestros resultados por grupo.ping valores idénticos juntos, exactamente como sugiere la ilustración anterior.

Primero, ejecutemos una consulta sencilla.

SELECT `movie_id` FROM `movierentals`;

Ahora la misma consulta con la palabra clave DISTINCT:

SELECT DISTINCT `movie_id` FROM `movierentals`;

DISTINCT omite los registros duplicados:

COUNT vs COUNT(*) vs COUNT(DISTINCT): ¿Cuál debería usar?

DISTINCT también se puede colocar interior una función agregada, y aquí es donde la mayoría de los principiantes pierden track de las cuales se cuentan realmente las filas. Los cuatro formularios que aparecen a continuación se ejecutan sobre la misma tabla de cinco filas llamada movierentals que se mostró anteriormente, pero no todos devuelven el mismo número. La diferencia radica en dos preguntas: ¿el formulario cuenta filas o valores?, y ¿conserva los duplicados?

SELECT COUNT(*) AS `all_rows`,
       COUNT(`return_date`) AS `returned_rows`,
       COUNT(DISTINCT `movie_id`) AS `unique_movies`
FROM `movierentals`;

💡 Consejo: Utilice COUNT(*) para contar filas, COUNT(columna) cuando NULL signifique "no aplica" y COUNT(DISTINCT columna) para valores únicos. Lo opuesto a DISTINCT es ALL, que es el valor predeterminado y, por lo tanto, rara vez se escribe.

Función MIN

La función MÍN. devuelve el valor más pequeño en el campo de la tabla especificada.

Supongamos que queremos saber el año en que se estrenó la película más antigua de nuestra colección. MySQLLa función MIN nos da eso.

SELECT MIN(`year_released`) FROM `movies`;

Resultado:

Función MAX

Tal como sugiere el nombre, la función MAX es lo opuesto a la función MIN. Él devuelve el valor más grande del campo de tabla especificado.

Supongamos que queremos saber el año de estreno de la última película de nuestra base de datos. El siguiente ejemplo nos lo devuelve.

SELECT MAX(`year_released`) FROM `movies`;

Resultado:

Función SUMA

MIN y MAX seleccionan un valor existente de una columna. SUMA y AVG Calcula un nuevo número a partir de toda la columna.

Supongamos que queremos el monto total de pagos realizados hasta el momento. MySQL SUM función Devuelve la suma de todos los valores en la columna especificada.. SUM sólo funciona en campos numéricos, y Los valores NULL se excluyen del resultado..

La siguiente tabla muestra los datos de la tabla de pagos.

La consulta que se muestra a continuación obtiene todos los pagos realizados y los suma en un único resultado: 2500 + 2000 + 6000 = 10500.

SELECT SUM(`amount_paid`) FROM `payments`;

Resultado:

AVG función

El MySQL AVG función devuelve el promedio de los valores en una columna especificada. Al igual que la función SUMA, funciona sólo en tipos de datos numéricos.

Supongamos que queremos calcular el importe medio pagado. Podemos usar la siguiente consulta, que divide el total de 10500 entre las tres filas de pago que no son nulas.

SELECT AVG(`amount_paid`) FROM `payments`;

Resultado:

⚠️ Advertencia: AVG divide por el número de filas no nulas, no por el número de filas de la tabla. Una cantidad nula se omite en lugar de contarse como cero, lo que aumenta silenciosamente el promedio. Usar AVG(IFNULL(`amount_paid`, 0)) cuando un valor faltante significa cero.

Ejemplo práctico: Combinación de funciones de agregación con GROUP BY

Cada una de las funciones anteriores devolvió una cifra para toda la tabla. Agregar una GRUPO POR La cláusula devuelve una cifra por grupo En cambio, así es como se elaboran los informes reales.

El siguiente ejemplo agrupa a los miembros por nombre y luego cuenta el número total de pagos, el importe medio de cada pago y el total general de los importes de pago para cada miembro.

SELECT m.`full_names`,
       COUNT(p.`payment_id`) AS `paymentscount`,
       AVG(p.`amount_paid`) AS `averagepaymentamount`,
       SUM(p.`amount_paid`) AS `totalpayments`
FROM members m, payments p
WHERE m.`membership_number` = p.`membership_number`
GROUP BY m.`full_names`;

Ejecutando el ejemplo anterior en MySQL Workbench nos da los siguientes resultados.

La consulta une las dos tablas en la cláusula WHERE, el estilo de unión por comas más antiguo. El código moderno escribe la misma lógica como una consulta explícita. UNIÓN INTERNA … ENCENDIDA. Tenga en cuenta también que cada columna no agregada en la lista SELECT debe aparecer en GROUP BY, o MySQL 5.7 y posteriormente rechazan la consulta bajo ONLY_FULL_GROUP_BY. Consulte el oficial MySQL Referencia de función agregada.

Preguntas Frecuentes