SQL · Guides · October 30, 2026 · 9 min read

SQL aggregate functions: COUNT, SUM, AVG, MIN, MAX with examples

An aggregate function takes many rows and returns one value. The five you will use every day are COUNT (how many), SUM (total), AVG (average), MIN and MAX (smallest and largest). Without GROUP BY they collapse the whole table into one row; with GROUP BY they return one row per group. Two rules explain most surprises: all of them except COUNT(*) ignore NULL, and COUNT(*), COUNT(column) and COUNT(DISTINCT column) count three different things.

All examples run on the airline database from the Nomto SQL course, in PostgreSQL syntax on DuckDB (the engine of the Nomto SQL room), and every result table is the real output. Fares are in US dollars.

The five aggregate functions in one query

ticket_flights has one row per passenger per flight segment, with the fare in amount:

SELECT COUNT(*)              AS segments,
       SUM(amount)           AS revenue,
       ROUND(AVG(amount), 2) AS avg_fare,
       MIN(amount)           AS min_fare,
       MAX(amount)           AS max_fare
FROM ticket_flights;

COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)

This is the difference that matters most in practice:

SELECT COUNT(*)                        AS flights,
       COUNT(actual_departure)         AS departed,
       COUNT(DISTINCT arrival_airport) AS destinations,
       COUNT(DISTINCT aircraft_code)   AS models_used
FROM flights;

• COUNT(*) counts rows, whatever is in them: 33,121 flights.
• COUNT(actual_departure) counts rows where that column is not NULL: only 16,773 flights have actually left; the rest are still in the future or were cancelled.
• COUNT(DISTINCT arrival_airport) counts different values: 104 destinations. NULLs are not counted as a value.

COUNT(1) gives the same result as COUNT(*) and is not faster in any modern database. Pick one and stay consistent.

COUNT(DISTINCT ...) is how you answer “how many unique X”. The tickets table has one row per passenger ticket, and several tickets can share one booking:

SELECT COUNT(*)                      AS tickets,
       COUNT(contact_data->>'email') AS with_email,
       COUNT(DISTINCT book_ref)      AS bookings
FROM tickets;

To count distinct combinations of two columns, PostgreSQL and DuckDB do not accept COUNT(DISTINCT a, b) (MySQL does). Count the rows of a SELECT DISTINCT instead:

SELECT COUNT(*) AS routes
FROM (SELECT DISTINCT departure_airport, arrival_airport FROM flights) r;

SUM and AVG

SUM adds up, AVG divides that sum by the number of non-NULL values. Both only accept numbers. Per fare class, sorted by average fare:

SELECT fare_conditions,
       COUNT(*)              AS segments,
       ROUND(SUM(amount))    AS revenue,
       ROUND(AVG(amount), 2) AS avg_fare,
       MIN(amount)           AS min_fare,
       MAX(amount)           AS max_fare
FROM ticket_flights
GROUP BY fare_conditions
ORDER BY avg_fare DESC;

The average of averages trap

What is the average fare? Averaging the three class averages gives a very different answer from averaging all segments:

SELECT ROUND(AVG(avg_fare), 2) AS avg_of_averages,
       (SELECT ROUND(AVG(amount), 2) FROM ticket_flights) AS true_average
FROM (
  SELECT fare_conditions, AVG(amount) AS avg_fare
  FROM ticket_flights
  GROUP BY fare_conditions
) t;

The average of averages gives 17,291 Comfort segments the same weight as 920,793 Economy ones. Always average the raw rows, or compute SUM(amount) / SUM(segments) from grouped totals.

Integer division

In PostgreSQL, SQL Server and SQLite, dividing one integer by another drops the remainder: COUNT(*) / COUNT(*)-style ratios silently become 0 or 1. DuckDB’s / returns a fraction (7 / 2 is 3.5; 7 // 2 is 3), and MySQL’s / does too. Multiplying by 100.0 first, as in the examples below, gives the right answer everywhere.

MIN and MAX

MIN and MAX work on any type that can be sorted: numbers, dates and text.

SELECT MIN(book_date)    AS first_booking,
       MAX(book_date)    AS last_booking,
       MIN(total_amount) AS smallest,
       MAX(total_amount) AS largest
FROM bookings;

On text, MIN(city) returns the alphabetically first value (Abakan) and MAX(city) the last (Yuzhno-Sakhalinsk). MAX returns the value, not the row: to see which booking had the largest amount, use ORDER BY total_amount DESC LIMIT 1, or a window function when you need it per group (see window functions).

Aggregate functions and NULL

• SUM, AVG, MIN, MAX and COUNT(col) skip NULLs. AVG of 1, NULL, 3 is 2, not 4/3.
• COUNT(*) counts every row, NULLs included.
• Over zero rows, COUNT returns 0, but SUM, AVG, MIN and MAX return NULL. Wrap them in COALESCE(SUM(x), 0) when a report should show 0.
• Replacing NULL with 0 before averaging (AVG(COALESCE(x, 0))) changes the answer; do it only when a missing value really means zero.

The NULL guide shows each of these on real data, including how much an average shifts when unknown values are turned into zeros.

Aggregates with GROUP BY and HAVING

GROUP BY splits rows into groups, and each aggregate is computed once per group. HAVING then filters groups by an aggregate:

SELECT departure_airport, COUNT(*) AS departures
FROM flights
GROUP BY departure_airport
HAVING COUNT(*) >= 1500
ORDER BY departures DESC;

Two rules follow from the order in which SQL runs a query:

• Aggregates are not allowed in WHERE, which runs before grouping. DuckDB says WHERE clause cannot contain aggregates!; filter on aggregates with HAVING.
• Every column in SELECT must be either in GROUP BY or inside an aggregate. Otherwise the database cannot know which of the group’s values to show.

The details, with the common mistakes, are in GROUP BY vs HAVING.

Conditional aggregates: FILTER and CASE

Often you want several counts with different conditions in one row. The FILTER clause applies a condition to one aggregate only:

SELECT COUNT(*) FILTER (WHERE status = 'Cancelled') AS cancelled,
       COUNT(*) FILTER (WHERE status = 'Delayed')   AS delayed,
       SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled_case,
       ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'Cancelled') / COUNT(*), 2) AS cancelled_pct
FROM flights;

FILTER works in PostgreSQL (9.4+), DuckDB and SQLite (3.30+). MySQL and SQL Server do not support it; there, use SUM(CASE WHEN ... THEN 1 ELSE 0 END) or COUNT(CASE WHEN ... THEN 1 END), which return the same number, as the cancelled_case column shows.

The same pattern gives shares of a total in one pass:

SELECT ROUND(SUM(amount)) AS revenue,
       ROUND(SUM(amount) FILTER (WHERE fare_conditions = 'Business')) AS business_revenue,
       ROUND(100.0 * SUM(amount) FILTER (WHERE fare_conditions = 'Business') / SUM(amount), 1) AS business_pct
FROM ticket_flights;

Business class is about 10% of segments but 26.5% of revenue. More conditional logic is in the CASE WHEN guide.

Wrong totals after a JOIN

Aggregates count whatever rows they get. A join that matches one row to several repeats it, and SUM adds it several times. Booking totals summed after joining to tickets:

SELECT SUM(b.total_amount) AS wrong_revenue,
       (SELECT SUM(total_amount) FROM bookings) AS right_revenue
FROM bookings b
JOIN tickets t ON t.book_ref = b.book_ref;

A booking with three passengers has three tickets, so its total was added three times. The query runs without errors and the number looks plausible. Aggregate each table at its own level first, then join the results, or use COUNT(DISTINCT ...) when you only need counts. The joins guide explains this fan-out in detail.

Nesting aggregates

You cannot put one aggregate directly inside another: SELECT MAX(COUNT(*)) FROM flights GROUP BY departure_airport fails with “aggregate function calls cannot be nested”. Aggregate in a subquery or CTE, then aggregate the result:

SELECT AVG(n) AS avg_tickets_per_booking, MAX(n) AS max_tickets
FROM (SELECT book_ref, COUNT(*) AS n FROM tickets GROUP BY book_ref) t;

On average a booking has 1.4 tickets, and the largest has five. See the CTE guide for writing multi-step aggregations readably.

Other useful aggregate functions

SELECT split_part(model, ' ', 1) AS maker,
       COUNT(*)   AS models,
       MAX(range) AS max_range,
       STRING_AGG(model, ', ' ORDER BY range DESC) AS line_up
FROM aircrafts
GROUP BY maker
ORDER BY max_range DESC;

• STRING_AGG(col, ', ') joins values into one string (MySQL: GROUP_CONCAT).
• percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) gives the median: 134.00 here, well below the 198.59 average, because a few expensive Business fares pull the mean up. DuckDB also has median(amount). MySQL has no built-in percentile aggregate.
• BOOL_AND, BOOL_OR check whether a condition holds for all or any rows in a group (PostgreSQL, DuckDB).
• ARRAY_AGG collects values into an array.

Every aggregate can also be used as a window function with OVER (...), which keeps the individual rows instead of collapsing them: that is how running totals and shares of a group are computed.

FAQ

What are the aggregate functions in SQL?

The standard five are COUNT, SUM, AVG, MIN and MAX. Most databases add more, such as STRING_AGG, ARRAY_AGG, STDDEV, VARIANCE and percentile functions. They all turn a set of rows into a single value, per group if there is a GROUP BY.

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts rows. COUNT(column) counts rows where the column is not NULL. In the flights table they give 33,121 and 16,773 for actual_departure, because flights that have not left yet have no departure time.

Do aggregate functions ignore NULL?

Yes, all of them except COUNT(*). AVG divides by the number of non-NULL values, and SUM of a column that is entirely NULL (or of zero rows) returns NULL, not 0.

Can I use an aggregate function in WHERE?

No. WHERE filters rows before they are grouped, so aggregates do not exist yet. Use HAVING to filter on an aggregate, or compute it in a subquery and filter in the outer query.

Can I use aggregate functions without GROUP BY?

Yes. Without GROUP BY the whole table (after WHERE) is one group, and you get a single row. You cannot mix such aggregates with plain columns in the same SELECT.

How do I count distinct values in SQL?

COUNT(DISTINCT column). For combinations of several columns, count the rows of SELECT DISTINCT a, b in a subquery, since COUNT(DISTINCT a, b) works only in MySQL.

Also in Español, Português (Brasil), Tiếng Việt, 简体中文