COUNT(*) FILTER (WHERE ...): agregados condicionales en una sola pasada

Cuando necesitas varias metricas bajo condiciones distintas en una sola consulta — por ejemplo, pedidos con estado paid y, por separado, refunded — la clausula FILTER (WHERE ...) las calcula todas en una unica pasada sobre los datos. Es SQL estandar y mucho mas legible que el viejo truco de SUM(CASE WHEN ...).

Que es FILTER y por que importa

FILTER (WHERE ...) se acopla a cualquier funcion de agregacion y restringe las filas que entran en ella. El agregado solo ve las filas donde la condicion es verdadera; el resto se ignora, como si no formaran parte del grupo.

SELECT
  count(*)                                  AS orders_total,
  count(*) FILTER (WHERE status = 'paid')   AS paid_orders,
  count(*) FILTER (WHERE status = 'refunded') AS refunded_orders
FROM orders;

Propiedades clave:

• FILTER se aplica en el momento de agregar, por encima del WHERE general de la consulta.
• Dentro de un mismo SELECT, cada agregado puede llevar su propio FILTER.
• La condicion es una expresion booleana completa sobre las columnas de la fila.

Varios segmentos en una sola pasada

La verdadera ventaja es armar un informe ancho con una docena de metricas sin recorrer la tabla varias veces y sin autouniones. Encaja de forma natural con GROUP BY.

SELECT
  u.country,
  count(*)                                          AS users_total,
  count(*) FILTER (WHERE o.id IS NOT NULL)          AS users_with_orders,
  sum(o.amount) FILTER (WHERE o.status = 'paid')    AS revenue_paid,
  avg(o.amount) FILTER (WHERE o.status = 'paid')    AS avg_paid_order
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.country;

Una pasada, un plan de ejecucion: el motor calcula todos los agregados a la vez. Es mas rapido y mas claro que tres consultas casi identicas unidas con JOIN.

FILTER frente a CASE dentro del agregado

El mismo resultado solia escribirse con CASE dentro de SUM/COUNT. Compara:

-- Old style: CASE inside the aggregate
SELECT
  count(CASE WHEN status = 'paid' THEN 1 END)     AS paid_orders,
  sum(CASE WHEN status = 'paid' THEN amount END)  AS revenue_paid
FROM orders;

-- Modern equivalent with FILTER
SELECT
  count(*)      FILTER (WHERE status = 'paid') AS paid_orders,
  sum(amount)   FILTER (WHERE status = 'paid') AS revenue_paid
FROM orders;

FILTER gana en legibilidad: la condicion queda separada de la expresion, asi que se ve de inmediato que mide cada metrica. Un matiz con count: count(*) FILTER (WHERE ...) y count(CASE WHEN ... THEN 1 END) dan el mismo numero, porque count nunca cuenta NULL.

Combinaciones utiles:

• Proporcion del segmento: count(*) FILTER (WHERE status = 'paid')::numeric / count(*).
• Varios umbrales: FILTER separados para salary > 100000, salary > 200000, etc.
• Distintos con condicion: count(DISTINCT user_id) FILTER (WHERE amount > 0).

Gotcha: NULL, grupos vacios y portabilidad

Algunas trampas faciles de pisar:

• Si ninguna fila pasa el FILTER, count devuelve 0, pero sum/avg/max devuelven NULL, no cero. Envuelvelos en COALESCE cuando necesites 0.

-- High earners per department, 0 instead of NULL when none match
SELECT
  dept,
  coalesce(sum(salary) FILTER (WHERE salary > 150000), 0) AS top_payroll
FROM employees
GROUP BY dept;

• FILTER no puede combinarse con funciones de ventana usando OVER en la mayoria de los motores: es solo para agregados.
• Soporte por motor:
  • PostgreSQL (9.4+), SQLite (3.30+) y DuckDB admiten FILTER de forma nativa.
  • MySQL/MariaDB y versiones antiguas de otras bases no entienden la sintaxis FILTER; usa el respaldo SUM(CASE WHEN ...).
  • ClickHouse sigue su propio camino con combinadores como countIf(cond) y sumIf(col, cond).

-- Portable fallback for MySQL and older engines
SELECT
  sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)     AS paid_orders,
  sum(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS revenue_paid
FROM orders;

En resumen: en PostgreSQL y motores compatibles, usa FILTER; es mas limpio y mas rapido para informes segmentados. Donde no exista, ten a mano el equivalente con SUM(CASE WHEN ...).