Aggregate Functions

An aggregate function takes many rows as input and collapses them down into a single summary value. Where a normal expression operates row by row, an aggregate function looks across a whole set of rows — a table, a filtered subset, or a group — and produces one answer for that set.

This is one of the most powerful ideas in SQL: instead of pulling raw rows back to your application and summarizing them in code, you ask the database to do the summarizing for you.

The core aggregate functions

Function	What it computes
COUNT	Number of rows (or non-NULL values in a column)
SUM	Total of a numeric column
AVG	Average (mean) of a numeric column
MIN	Smallest value in a column
MAX	Largest value in a column

Basic usage

orders

SQL

SELECT
  COUNT(*)         AS total_orders,
  SUM(total)       AS revenue,
  AVG(total)       AS average_order_value,
  MIN(total)       AS smallest_order,
  MAX(total)       AS largest_order
FROM orders;

total_orders | revenue | average_order_value | smallest_order | largest_order
-------------+---------+----------------------+-----------------+---------------
         148 | 9840.50 |                66.49 |            5.00 |        450.00

Aggregates ignore NULLs

By default, every aggregate function (except COUNT(*)) skips NULL values when computing its result. A `SUM(discount)` on a column where some rows have no discount at all simply adds up the rows that do have a value — it does not treat `NULL` as zero. See the dedicated NULL-handling page for the full details, and the COUNT and SUM/AVG pages in this section for worked examples of exactly how this plays out.

Note

This NULL-skipping behavior is consistent across COUNT(column), SUM, AVG, MIN, and MAX. Only COUNT(*) counts rows regardless of whether their columns contain NULL.

Aggregates without GROUP BY summarize everything

When you use an aggregate function without a GROUP BY clause, the entire result set (after any WHERE filtering) is treated as a single group, and the query returns exactly one row.

SQL

-- One row summarizing ALL rows in the (filtered) table
SELECT COUNT(*) AS delivered_orders, SUM(total) AS delivered_revenue
FROM orders
WHERE status = 'delivered';

Tip

Want per-category summaries instead of one grand total — for example, revenue broken down by region or by customer? That is exactly what GROUP BY is for. See the GROUP BY page in this section to combine it with these same aggregate functions.

Aggregate functions collapse many rows into one summary value.
COUNT, SUM, AVG, MIN, and MAX are the core aggregate functions.
All aggregates except COUNT(*) ignore NULL values.
Without GROUP BY, an aggregate summarizes the entire (filtered) result into one row.
With GROUP BY, an aggregate produces one summary row per group.