Aggregate Functions

An aggregate function takes many rows and reduces them down to a single value: a count, a sum, an average, and so on. Aggregates are the foundation of reporting and analytics queries, and they pair naturally with GROUP BY (covered next in this series) once you need one summary value per group rather than one summary value overall.

The essentials

Function	Returns
`COUNT(*)`	Number of rows
`COUNT(col)`	Number of non-NULL values in `col`
`SUM(col)`	Total of all values in `col`
`AVG(col)`	Average of all values in `col`
`MIN(col)`	Smallest value in `col`
`MAX(col)`	Largest value in `col`
`ARRAY_AGG(col)`	All values of `col` collected into a PostgreSQL array
`STRING_AGG(col, sep)`	All values of `col` concatenated into one string, separated by `sep`

COUNT, SUM, AVG, MIN, and MAX over all orders

SQL

SELECT COUNT(*)         AS order_count,
       SUM(total_amount) AS revenue,
       AVG(total_amount) AS avg_order_value,
       MIN(total_amount) AS smallest_order,
       MAX(total_amount) AS largest_order
FROM orders;

order_count | revenue  | avg_order_value | smallest_order | largest_order
------------+----------+-----------------+-----------------+---------------
        842 | 61350.75 |           72.86 |            9.99 |        499.00

ARRAY_AGG and STRING_AGG

These two are PostgreSQL specialties — genuinely convenient aggregates that are not as universally available in other database systems.

All product names in a single order, as an array

SQL

SELECT o.order_id, ARRAY_AGG(p.name) AS products
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.order_id = 1042
GROUP BY o.order_id;

order_id | products
---------+--------------------------------
    1042 | {"Wireless Mouse","USB-C Cable"}

The same thing as a readable, comma-separated string

SQL

SELECT o.order_id, STRING_AGG(p.name, ', ') AS products
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.order_id = 1042
GROUP BY o.order_id;

order_id | products
---------+---------------------------
    1042 | Wireless Mouse, USB-C Cable

STRING_AGG also accepts an ORDER BY inside the function call (for example STRING_AGG(p.name, ', ' ORDER BY p.name)) so you can control the order items appear in, independent of the query’s own ORDER BY.

Aggregates ignore NULLs by default

Every aggregate in the table above skips NULL values rather than treating them as zero or including them in the count. AVG(discount) over a column where most rows have no discount recorded (NULL) averages only the rows that actually have a value — it does not divide by the total row count. The one exception is COUNT(*), which counts rows regardless of any column’s value.

Aggregates without GROUP BY summarize everything

When a query uses an aggregate function but no GROUP BY clause, PostgreSQL treats the entire result set as a single group and returns exactly one row.

One row summarizing the whole products table

SQL

SELECT COUNT(*) AS total_products, AVG(price) AS avg_price
FROM products;

The next page in this series, GROUP BY, shows how to get one summary row per group instead of one summary row for the entire table.

Aggregate functions reduce many rows down to one value
COUNT, SUM, AVG, MIN, MAX are the universal building blocks
ARRAY_AGG and STRING_AGG are PostgreSQL-specific and very convenient
All aggregates ignore NULLs, except COUNT(*)
Without GROUP BY, an aggregate summarizes the entire result set into one row