Al igual que la mayoría de los demás productos de bases de datos relacionales, PostgreSQL soporta funciones de agregación. Una función de agregación calcula un único resultado a partir de múltiples filas de entrada. Por ejemplo, existen agregaciones para calcular count (cuenta), sum (suma), avg (promedio), max (máximo) y min (mínimo) sobre un conjunto de filas.

Como ejemplo, podemos encontrar la lectura de temperatura mínima más alta en cualquier lugar con:

SELECT max(temp_lo) FROM weather;

  max
-----
   46
(1 row)

Si quisiéramos saber en qué ciudad (o ciudades) se produjo esa lectura, podríamos intentar:

SELECT city FROM weather WHERE temp_lo = max(temp_lo);     -- INCORRECTO

pero esto no funcionará ya que la función de agregación max no se puede utilizar en la cláusula WHERE. (Esta restricción existe porque la cláusula WHERE determina qué filas se incluirán en el cálculo de la agregación; por lo que obviamente tiene que ser evaluada antes de que se calculen las funciones de agregación). Sin embargo, como suele ser el caso, la consulta se puede reformular para lograr el resultado deseado, en este caso mediante el uso de una subconsulta:

SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

     city
---------------
 San Francisco
(1 row)

Esto es correcto porque la subconsulta es un cálculo independiente que computa su propia agregación por separado de lo que está sucediendo en la consulta externa.

Las agregaciones también son muy útiles en combinación con las cláusulas GROUP BY. Por ejemplo, podemos obtener el número de lecturas y la temperatura mínima máxima observada en cada ciudad con:

SELECT city, count(*), max(temp_lo)
    FROM weather
    GROUP BY city;

     city      | count | max
---------------+-------+-----
 Hayward       |     1 |  37
 San Francisco |     2 |  46
(2 rows)

lo que nos da una fila de salida por ciudad. Cada resultado de agregación se calcula sobre las filas de la tabla que coinciden con esa ciudad. Podemos filtrar estas filas agrupadas utilizando la cláusula HAVING:

SELECT city, count(*), max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;

   city   | count | max
---------+-------+-----
 Hayward |     1 |  37
(1 row)

lo que nos da los mismos resultados pero solo para las ciudades que tienen todos los valores de temp_lo por debajo de 40. Finalmente, si solo nos importan las ciudades cuyos nombres comienzan con “S”, podríamos hacer lo siguiente:

SELECT city, count(*), max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'            -- (1)
    GROUP BY city;

     city      | count | max
---------------+-------+-----
 San Francisco |     2 |  46
(1 row)

Es importante entender la interacción entre las agregaciones y las cláusulas WHERE y HAVING de SQL. La diferencia fundamental entre WHERE y HAVING es la siguiente: WHERE selecciona las filas de entrada antes de que se calculen los grupos y las agregaciones (por lo tanto, controla qué filas entran en el cálculo de la agregación), mientras que HAVING selecciona las filas de grupo después de que se calculen los grupos y las agregaciones. Por lo tanto, la cláusula WHERE no debe contener funciones de agregación; no tiene sentido intentar utilizar una agregación para determinar qué filas serán las entradas de las agregaciones. Por otro lado, la cláusula HAVING siempre contiene funciones de agregación. (Estrictamente hablando, se te permite escribir una cláusula HAVING que no utilice agregaciones, pero rara vez es útil. La misma condición podría utilizarse de manera más eficiente en la etapa de WHERE).

En el ejemplo anterior, podemos aplicar la restricción del nombre de la ciudad en WHERE, ya que no necesita agregación. Esto es más eficiente que añadir la restricción a HAVING, porque evitamos hacer el agrupamiento y los cálculos de agregación para todas las filas que no superen la comprobación de la cláusula WHERE.

Otra forma de seleccionar las filas que entran en un cálculo de agregación es utilizar FILTER, que es una opción por agregación:

SELECT city, count(*) FILTER (WHERE temp_lo < 45), max(temp_lo)
    FROM weather
    GROUP BY city;

     city      | count | max
---------------+-------+-----
 Hayward       |     1 |  37
 San Francisco |     1 |  46
(2 rows)

FILTER es muy similar a WHERE, excepto que elimina filas únicamente de la entrada de la función de agregación particular a la que está asociado. Aquí, la agregación count cuenta solo las filas con temp_lo por debajo de 45; pero la agregación max se sigue aplicando a todas las filas, por lo que sigue encontrando la lectura de 46.