Funciones de agregación — COUNT / SUM / AVG / MIN / MAX

Los datos que vamos a usar — la tabla score

En este artículo trabajamos con las funciones de agregación (funciones que pliegan muchas filas en un solo valor). Cuántas filas hay, cuál es el total, el promedio, el mínimo, el máximo — todo esto comprime muchas filas en un solo número. Las cinco básicas son COUNT / SUM / AVG / MIN / MAX, y casi todas las cifras resumidas de un informe se pueden construir solo con estas.

El conjunto de datos es la tabla score (30 filas = 10 personas × las 3 materias Math / English / Science). Apuntando a los puntos de la columna score, primero trabajarás las agregaciones de toda la tabla, y luego las agregaciones tras restringir las filas con WHERE. Las agregaciones por grupo (como el promedio por materia) se tratan en el artículo siguiente, así que aquí nos centramos en plegar toda la tabla — o todo el rango restringido por WHERE — en un solo valor.

Antes de meterte en los ejercicios, échale un vistazo a las definiciones de columnas y a una muestra de datos de la tabla score.

① Ejecuta PRAGMA table_info(score); para comprobar los nombres de columnas, los tipos y la clave primaria.

② Ejecuta SELECT * FROM score LIMIT 5; para previsualizar las primeras 5 filas de datos.

Las funciones de agregación: «muchas filas → un valor»

Las funciones de agregación van en la posición de una columna de un SELECT.

Lo que hay que vigilar aquí es no mezclar una columna normal y una función de agregación en el mismo `SELECT`. Una columna normal como name tiene un valor distinto por fila, mientras que una función de agregación como COUNT(*) pliega toda la tabla (o todo el conjunto restringido por `WHERE`) en un solo valor. Si escribes ambas, como en SELECT name, COUNT(*) FROM score;, el lado de la agregación es un único valor para todo el conjunto mientras que name no tiene respuesta definida a «¿el valor de qué fila debo mostrar?». En la mayoría de las bases de datos esto es un error.

-- Obtener juntos el recuento, el recuento distinto, el total y el promedio
SELECT
  COUNT(*)             AS row_count,
  COUNT(DISTINCT name) AS name_kinds,
  SUM(score)           AS total,
  AVG(score)           AS avg_raw
FROM score
WHERE subject = 'English';

La diferencia entre COUNT(*) y COUNT(columna)

COUNT(*) cuenta el número de filas en sí. COUNT(columna) cuenta solo las filas donde esa columna no es NULL. La tabla score no tiene NULL, así que ambos dan el mismo 30, pero para una columna que contiene NULL verás una diferencia aquí (lo confirmaremos más adelante usando una tabla con NULL). Además, escribir COUNT(DISTINCT columna) cuenta el número de tipos distintos con los duplicados eliminados (úsalo en subject y obtienes 3, el número de materias distintas).

Consejo — las funciones de agregación y NULL

SUM / AVG / MIN / MAX no incluyen las filas NULL en el cálculo (las ignoran). En particular, AVG se calcula como la suma de los valores no NULL ÷ el recuento de valores no NULL. Por eso, para una columna que contiene NULL, SUM(columna) / COUNT(*) (denominador = todas las filas) y AVG(columna) (denominador = recuento de no NULL) dan resultados distintos. Cuando quieras promediar tratando NULL como 0, deletréalo explícitamente con algo como AVG(COALESCE(columna, 0)).

Imagina un requisito: «Quiero mostrar la escala global de los datos de notas en un panel en una sola fila». (Ejecútalo correctamente y aparecerá la explicación.)

① Desde la tabla score, usa las cinco funciones de agregación para extraer las siguientes 5 columnas en una sola fila.

② Pon como alias row_count al recuento de todas las filas, total a la suma de los puntos, avg_score al promedio de los puntos, min_score al punto más bajo y max_score al punto más alto, en ese orden.

③ Como el promedio se muestra con un decimal largo, redondéalo a 2 decimales y ponle el alias avg_score.

Restringir el objetivo con WHERE antes de agregar

Añadir WHERE hace que solo las filas que cumplen la condición sean la entrada de las funciones de agregación. El orden de ejecución es «FROM lee todas las filas → WHERE restringe las filas → las filas restantes se pliegan con las funciones de agregación». Es decir, las funciones de agregación solo ven las filas que pasaron WHERE, y la suma o el promedio se calcula tras el filtrado. Añade WHERE subject = 'Math' y solo las 10 filas de Math son el objetivo, así que el promedio se convierte en el promedio de Math únicamente.

-- Recuento / promedio / nota máxima solo para las 10 filas de Science
SELECT
  COUNT(*)             AS row_count,
  ROUND(AVG(score), 1) AS avg_science,
  MAX(score)           AS top_science
FROM score
WHERE subject = 'Science';

Imagina un requisito: «Quiero recortar solo las notas de Math y producir el promedio, el máx y el mín».

① Desde la tabla score, apunta solo a las filas donde subject es Math.

② Extrae, en una sola fila, el recuento de filas objetivo con el alias math_count, la nota media con avg_math, el punto más alto con max_math y el punto más bajo con min_math, en ese orden.

③ Redondea el promedio a 2 decimales.

COUNT(columna) y NULL, y el redondeo de AVG

La tabla score hasta ahora no tenía NULL. Confirmemos cómo cambia la agregación según si hay NULL o no, usando la tabla customer que tiene huecos (8 personas, con NULL en age / email / country). COUNT(*) es el número de filas, así que se queda en 8, pero COUNT(email) cuenta solo las filas donde email está rellenado, así que es 4. Ejecutar COUNT(columna) sobre una columna que contiene NULL da un resultado más pequeño que COUNT(*) así.

Además, como AVG es una división, produce decimales como 31.5. En un informe es más fácil de manejar si redondeas con ROUND(expresión, dígitos), como en ROUND(AVG(age), 2).

-- Cómo cambian COUNT y AVG con NULL presentes
SELECT
  COUNT(*)            AS rows_all,
  COUNT(country)      AS with_country,
  ROUND(AVG(age), 2)  AS avg_age
FROM customer;

Imagina un requisito: «Los datos de los miembros tienen campos sin rellenar (NULL). Quiero comparar el recuento total con el recuento donde realmente hay un valor presente».

① Desde la tabla customer, extrae, en una sola fila, el recuento total de filas con el alias all_rows, el recuento donde email está rellenado con with_email, el recuento donde age está rellenado con with_age, y el número de valores distintos de country con country_kinds, en ese orden.

② Confirma que ejecutar COUNT(columna) sobre una columna con NULL da un resultado más pequeño que COUNT(*).

Imagina un requisito: «Quiero extraer solo las notas que superan el promedio global y ver su recuento y su promedio». El promedio global es 77.93… (lo confirmaste en el ejercicio anterior).

① Desde la tabla score, apunta solo a las filas donde score es 78 o más.

② Extrae, en una sola fila, el recuento de filas objetivo con el alias high_count, su nota media con avg_high, y el punto más alto con max_high, en ese orden.

③ Redondea el promedio a 2 decimales.