Agregación condicional en SQL con CASE

Lección 4.4 · Tiempo de lectura: ~8 min · Tareas de práctica ↓

La agregación condicional calcula varias métricas en una sola consulta SQL colocando una expresión CASE dentro de una función de agregación: SUM(CASE WHEN ... THEN ... END). En esta lección aprenderás sumas, conteos y promedios condicionales, los errores con ELSE que dan números incorrectos y cómo convertir filas en columnas (pivot).

Con GROUP BY obtienes un valor por grupo: la cantidad de pagos, el monto total. Los informes suelen necesitar más: para cada empleado, cuántos pagos fueron chicos, medianos y grandes, y cuánto aportó cada rango. Sin agregación condicional tendrías que ejecutar tres consultas con distintos filtros WHERE y combinar los resultados a mano.

La idea principal

Una función de agregación procesa todas las filas del grupo. Un CASE dentro de ella decide qué valor aporta cada fila:

SUM(CASE WHEN condition THEN value ELSE 0 END)
COUNT(CASE WHEN condition THEN 1 END)
AVG(CASE WHEN condition THEN value END)

Las filas que no cumplen la condición aportan 0 a una suma o NULL, que las funciones de agregación ignoran. Así, cada métrica se calcula solo sobre "sus" filas, mientras que todas las métricas se calculan en una sola pasada por la tabla.

Suma condicional

SELECT staff_id,
       SUM(CASE WHEN amount < 2 THEN amount ELSE 0 END) AS low_total,
       SUM(CASE WHEN amount >= 2 AND amount < 6 THEN amount ELSE 0 END) AS medium_total,
       SUM(CASE WHEN amount >= 6 THEN amount ELSE 0 END) AS high_total,
       SUM(amount) AS total
FROM payment
GROUP BY staff_id;

Resultado: para cada empleado, los ingresos de pagos chicos, medianos y grandes, y el total. Los tres rangos sumados dan el total.

Para SUM, ELSE 0 es lo correcto: las filas fuera del rango no agregan nada. Sin ELSE, un rango sin ninguna fila que coincida devuelve NULL en lugar de 0.

Conteo condicional

SELECT staff_id,
       COUNT(CASE WHEN amount < 2 THEN 1 END) AS low_payments,
       COUNT(CASE WHEN amount >= 2 AND amount < 6 THEN 1 END) AS medium_payments,
       COUNT(CASE WHEN amount >= 6 THEN 1 END) AS high_payments
FROM payment
GROUP BY staff_id;

Resultado: la cantidad de pagos en cada rango para cada empleado.

COUNT(expression) cuenta los valores que no son NULL. Cuando la condición es falsa y no hay ELSE, CASE devuelve NULL y la fila no se cuenta.

Un error común: COUNT(CASE WHEN ... THEN 1 ELSE 0 END). El cero no es NULL, así que COUNT cuenta todas las filas y las tres columnas muestran el mismo número. Si prefieres ELSE 0, usa SUM: SUM(CASE WHEN ... THEN 1 ELSE 0 END).

También se puede contar una columna directamente. COUNT(return_date) cuenta solo los alquileres devueltos:

SELECT staff_id,
       COUNT(*) AS all_rentals,
       COUNT(return_date) AS returned,
       COUNT(CASE WHEN return_date IS NULL THEN 1 END) AS not_returned
FROM rental
GROUP BY staff_id;

Resultado: para cada empleado, todos los alquileres, los devueltos y los que todavía no se devolvieron.

Promedio condicional

SELECT staff_id,
       AVG(amount) AS avg_payment,
       AVG(CASE WHEN amount >= 5 THEN amount END) AS avg_big_payment
FROM payment
GROUP BY staff_id;

Resultado: para cada empleado, el promedio de todos los pagos y el promedio de los pagos de $5 o más.

Para AVG, no escribas ELSE 0. El promedio es la suma dividida por la cantidad de valores; los ceros se contarían como valores y bajarían el promedio. Sin ELSE, las filas que no coinciden dan NULL y se omiten.

Pivot: convertir filas en columnas

Un pivot muestra los valores de una categoría como columnas separadas. Un GROUP BY rental_rate, rating normal devuelve una fila por combinación. Con agregación condicional, cada clasificación se convierte en una columna:

SELECT rental_rate,
       COUNT(CASE WHEN rating = 'G' THEN 1 END) AS g,
       COUNT(CASE WHEN rating = 'PG' THEN 1 END) AS pg,
       COUNT(CASE WHEN rating = 'PG-13' THEN 1 END) AS pg13,
       COUNT(CASE WHEN rating = 'R' THEN 1 END) AS r,
       COUNT(CASE WHEN rating = 'NC-17' THEN 1 END) AS nc17,
       COUNT(*) AS all_films
FROM film
GROUP BY rental_rate
ORDER BY rental_rate;

Resultado: tres filas, una por tarifa de alquiler ($0.99, $2.99, $4.99), con la cantidad de películas de cada clasificación en columnas separadas.

Lo mismo funciona con otras agregaciones. La duración promedio de las películas por clasificación, de nuevo sin ELSE:

SELECT rental_rate,
       ROUND(AVG(CASE WHEN rating = 'G' THEN length END), 1) AS g_avg_length,
       ROUND(AVG(CASE WHEN rating = 'R' THEN length END), 1) AS r_avg_length
FROM film
GROUP BY rental_rate
ORDER BY rental_rate;

Un pivot por día de la semana, armado a partir de una fecha:

SELECT MONTH(rental_date) AS rental_month,
       SUM(CASE WHEN DAYOFWEEK(rental_date) IN (1, 7) THEN 1 ELSE 0 END) AS weekend_rentals,
       SUM(CASE WHEN DAYOFWEEK(rental_date) BETWEEN 2 AND 6 THEN 1 ELSE 0 END) AS weekday_rentals
FROM rental
GROUP BY MONTH(rental_date)
ORDER BY rental_month;

Resultado: para cada mes, la cantidad de alquileres en fines de semana y en días hábiles. En MySQL, DAYOFWEEK() devuelve 1 para el domingo y 7 para el sábado.

Algunas bases de datos tienen un operador PIVOT (SQL Server, Oracle), pero la agregación condicional funciona en todas y es la opción portable.

Proporciones y porcentajes

Dos agregaciones condicionales se pueden combinar en una sola expresión:

SELECT customer_id,
       COUNT(*) AS payments,
       ROUND(100 * SUM(CASE WHEN amount >= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS big_payment_pct
FROM payment
GROUP BY customer_id
ORDER BY big_payment_pct DESC
LIMIT 10;

Resultado: los 10 clientes con la mayor proporción de pagos de $5 o más.

La cláusula FILTER

PostgreSQL y SQLite (3.30+) admiten una sintaxis estándar más corta, FILTER:

-- PostgreSQL, SQLite
SELECT customer_id,
       COUNT(*) AS all_payments,
       COUNT(*) FILTER (WHERE amount >= 5) AS big_payments,
       SUM(amount) FILTER (WHERE amount >= 5) AS big_total
FROM payment
GROUP BY customer_id;

Significa lo mismo que COUNT(CASE WHEN amount >= 5 THEN 1 END). MySQL, MariaDB y SQL Server no admiten FILTER, así que CASE sigue siendo la forma portable.

Conclusiones clave:

• La agregación condicional es una función de agregación con CASE dentro: varias métricas en una consulta y una pasada.
• SUM(CASE ... ELSE 0 END) para sumas; COUNT(CASE ... END) sin ELSE para conteos.
• COUNT(CASE ... ELSE 0 END) cuenta todas las filas: un error común.
• AVG(CASE ...) sin ELSE, si no los ceros bajan el promedio.
• Un pivot con CASE convierte los valores de una categoría en columnas y funciona en cualquier base de datos.

Preguntas frecuentes

¿Qué es la agregación condicional en SQL?

Es una función de agregación cuyo argumento es una expresión CASE, por ejemplo SUM(CASE WHEN amount >= 5 THEN amount ELSE 0 END). Cada métrica se calcula solo sobre las filas que cumplen su condición, así que una consulta puede devolver muchas métricas con filtros distintos.

¿Por qué COUNT(CASE WHEN ... ELSE 0 END) cuenta todas las filas?

COUNT cuenta los valores que no son NULL, y 0 no es NULL. Quita el ELSE para que las filas que no coinciden devuelvan NULL, o usa SUM(CASE WHEN ... THEN 1 ELSE 0 END).

¿Cómo convierto filas en columnas en MySQL?

MySQL no tiene operador PIVOT. Usa una agregación condicional por cada columna de salida, COUNT(CASE WHEN rating = 'G' THEN 1 END) AS g, y agrupa por la dimensión de las filas.