SQL Aggregate Functions: COUNT, SUM, AVG, MIN, MAX, and GROUP BY

You need one number out of a lot of rows: how many there are, or what they add up to. An aggregate function does that job. It reads a set of rows and returns a single value, and COUNT, SUM, AVG, MIN and MAX cover most of what a report asks for. Add GROUP BY and the query returns one value per group instead of one value for the whole table.

What an aggregate function returns

Five functions do almost all of the work, and they differ in what they read and what they hand back.

An aggregate goes in the SELECT list like any other expression, and AS gives the column it returns a name you choose. Written without GROUP BY, it treats every row the query selects as one single group, so the answer is one row however many rows went in. Every example below runs against this table:

CREATE TABLE sales (
    sale_id    INT,
    region     VARCHAR(50),
    product    VARCHAR(50),
    amount     DECIMAL(10, 2),
    sale_date  DATE
);

INSERT INTO sales VALUES
    (1, 'North', 'Widget',  120.00, '2025-01-10'),
    (2, 'South', 'Gadget',  200.00, '2025-01-15'),
    (3, 'North', 'Gadget',  180.00, '2025-02-01'),
    (4, 'East',  'Widget',   90.00, '2025-02-14'),
    (5, 'South', 'Widget',  150.00, '2025-03-05'),
    (6, 'North', NULL,       NULL,  '2025-03-12');

The last row is there on purpose. It records a sale in the North region with no product and no amount, which is how a table looks as soon as a column is optional, and it is the row that makes the difference between the counting forms visible.

MIN and MAX on any column you can sort

MIN and MAX work on anything the database knows how to order: numbers, dates and text. One query can ask for several of those ends at once.

SELECT
    MIN(amount)     AS lowest_sale,
    MAX(amount)     AS highest_sale,
    MIN(sale_date)  AS first_sale,
    MAX(sale_date)  AS latest_sale
FROM sales;

Six rows went in and one row came back. The row with no amount is not the lowest sale, because MIN and MAX skip the rows where the column is NULL and read the rest.

Adding GROUP BY turns the same question into one row per region:

SELECT region,
       MIN(amount) AS min_sale,
       MAX(amount) AS max_sale
FROM sales
GROUP BY region
ORDER BY region;

East has a single sale, so its smallest and its largest are the same row. North has three rows in the table but only two amounts, and the empty one moves neither end.

The three forms of COUNT

COUNT(*) counts rows. COUNT(column) counts the rows where that column holds a value. COUNT(DISTINCT column) counts how many different values there are. The three answers come apart as soon as a column is allowed to be empty.

SELECT
    COUNT(*)       AS total_rows,
    COUNT(amount)  AS rows_with_amount,
    COUNT(product) AS rows_with_product
FROM sales;

Six rows, five amounts, five products, and the difference is the one row where both columns are empty. That gap is worth reading as a data-quality number: it says how complete an optional column is, without a second query.

COUNT(DISTINCT column) answers the other question, how many different values appear rather than how many rows carry one:

SELECT COUNT(DISTINCT region) AS unique_regions
FROM sales;

Six rows produce three regions, because North appears three times and South twice.

SUM and AVG over a numeric column

SUM adds the values in a column. Rows where the column is empty contribute nothing, so no filter is needed to keep them out:

SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region
ORDER BY total_sales DESC;

The three North rows add up to 300.00 because the empty amount is skipped rather than read as a zero.

AVG divides that sum by the number of values it added, not by the number of rows in the group. ROUND keeps the result readable:

SELECT region, ROUND(AVG(amount), 2) AS average_sale
FROM sales
GROUP BY region
ORDER BY region;

The North average is 300.00 divided by 2, not by 3. Whether that is the number you want depends on what the empty amount means: a sale of nothing belongs in the denominator, a sale nobody has entered yet does not.

GROUP BY, HAVING, and several aggregates at once

GROUP BY splits the rows into groups before the aggregate runs. Name two columns and you get one row per combination that exists in the data:

SELECT region, product, SUM(amount) AS total
FROM sales
WHERE amount IS NOT NULL
GROUP BY region, product
ORDER BY region, product;

The WHERE clause drops the row with no amount before the grouping, which is why no group with an empty product appears in the result.

WHERE cannot test an aggregate, because at the moment it runs the groups do not exist yet. The clause for that is HAVING, which filters the groups once the aggregate has been computed:

SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
HAVING SUM(amount) > 100
ORDER BY total DESC;

East is gone: its single sale of 90.00 is the whole group total, and 90.00 is not over 100. For each clause on its own, see SQL GROUP BY Explained and SQL HAVING Clause.

A SELECT list can hold every aggregate the report needs, and the database computes all of them in one pass over the grouped rows:

SELECT
    region,
    COUNT(*)              AS orders,
    MIN(amount)           AS min_sale,
    MAX(amount)           AS max_sale,
    SUM(amount)           AS total,
    ROUND(AVG(amount), 2) AS average
FROM sales
WHERE amount IS NOT NULL
GROUP BY region
ORDER BY total DESC;

COUNT(*) counts the rows that survived the WHERE clause, so North shows two orders rather than the three rows it has in the table. One row per group and one column per question is the shape most reporting queries settle into.

A condition on one aggregate with FILTER or CASE

Sometimes only one of the aggregates should see a subset of the rows: a total for everything and a total for one region, side by side. WHERE cannot do it, because it applies to the whole query. The FILTER clause attaches a condition to a single aggregate, and PostgreSQL 17 accepts it: only the input rows for which the filter is true are fed to that function, and the others are discarded (PostgreSQL 17 documentation).

SELECT
    SUM(amount)                                 AS total_all,
    SUM(amount) FILTER (WHERE region = 'North') AS north_total,
    COUNT(*) FILTER (WHERE amount >= 150)       AS high_value_orders
FROM sales;

Where your engine rejects FILTER, a CASE expression inside the aggregate does the same job. A CASE with no ELSE returns NULL for the rows that do not match, and every aggregate except COUNT(*) skips NULL:

SELECT
    SUM(amount)                                     AS total_all,
    SUM(CASE WHEN region = 'North' THEN amount END) AS north_total,
    COUNT(CASE WHEN amount >= 150 THEN 1 END)       AS high_value_orders
FROM sales;

The same three numbers, written twice. FILTER says what it means at a glance and CASE runs on any engine, so the choice is between reading it and porting it.

Subtotals with ROLLUP, CUBE and GROUPING SETS

A report with a total per region and product usually wants the regional subtotals and the grand total in the same result. ROLLUP produces them: it stands for the given list of expressions and all prefixes of that list, down to the empty list (PostgreSQL 17 documentation). Every result in this section comes from PostgreSQL 17.

SELECT region, product, SUM(amount) AS total
FROM sales
WHERE amount IS NOT NULL
GROUP BY ROLLUP(region, product)
ORDER BY region, product;

The empty cells hold NULL, because a grouping column that a summary row does not group on is replaced by a null value. Those rows land at the end of each region because PostgreSQL sorts nulls as if larger than any non-null value, so ascending order puts them last.

CUBE takes the same list and groups by every subset of it, so the product totals across all regions appear as well:

SELECT region, product, SUM(amount) AS total
FROM sales
WHERE amount IS NOT NULL
GROUP BY CUBE(region, product)
ORDER BY region, product;

GROUPING SETS drops the shorthand and lets you name the levels you want. One total per region and one per product, with no combined rows and no grand total:

SELECT region, product, SUM(amount) AS total
FROM sales
WHERE amount IS NOT NULL
GROUP BY GROUPING SETS ((region), (product))
ORDER BY region, product;

MySQL 8.4 writes the first of the three as GROUP BY ROLLUP(region, product) too, and also accepts GROUP BY region, product WITH ROLLUP (MySQL 8.4 manual).

What NULL does to each function

One rule covers every function in this article except COUNT(*): a row whose value is NULL is not read at all. It is not counted as a zero, and it raises no error.

AVG is where that matters most, because the skipped rows leave the denominator as well as the numerator. COALESCE puts them back as zeros when zero is the right reading of a missing value:

SELECT
    ROUND(AVG(amount), 2)              AS avg_recorded,
    ROUND(AVG(COALESCE(amount, 0)), 2) AS avg_including_missing
FROM sales;

740.00 divided by 5, then the same 740.00 divided by 6. Both numbers are right and they answer different questions, so the one to publish is the one whose question the report asked. If nullable columns are new to you, see SQL NULL Values.

Aggregate functions vs window functions

A grouped aggregate replaces the rows it read with one row. A window function leaves the rows where they are and adds the summary next to them, which is what a report needs when it has to show a row and its group total together:

SELECT sale_id, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales
WHERE amount IS NOT NULL
ORDER BY region, sale_id;

PARTITION BY region is the GROUP BY of a window function: it says which rows share a total. Five rows go in, five rows come out, and the region total is repeated on each one. Write the grouped form when one row per group is the answer, and the window form when you want each row next to its group. Window Functions in SQL goes further into the second.

Mistakes that change the number you get

Confusing COUNT(*) with COUNT(column) is the expensive one, because both queries succeed and only one of them counts what you meant. Reach for COUNT(*) when you want rows, and for COUNT(column) when the missing values are the point.

Reading NULL as a zero is the same mistake one step later. An average over five recorded amounts and an average over six rows are different numbers, and the query text does not say which one the reader is looking at, so the column alias is worth spelling out.

A row-level condition written into HAVING is the third. WHERE runs before the rows are grouped and HAVING runs after it. A condition that only looks at one row's columns belongs in WHERE, because in HAVING it makes the database build the groups and then throw them away. A condition on SUM, COUNT or AVG has nowhere else to go and stays in HAVING.

The last one the database catches for you. Every column in the SELECT list is either inside an aggregate or in the GROUP BY list, because a column that is neither has no single value to show for the group. Add it to GROUP BY when you want finer groups, and wrap it in an aggregate when you do not. For examples narrowed to the counting functions, see SQL COUNT, AVG, and SUM Functions.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Build aggregate queries in DbSchema

Typing the GROUP BY list by hand is most of the work in a grouped query, and DbSchema's Query Builder assembles it from the diagram instead. Click a table header in the diagram and DbSchema opens the Query Builder loaded with that table, then turn on "Group By" mode with the toggle button in the Query Builder toolbar. In that mode ticked columns without an aggregate function become the GROUP BY columns, and right-clicking a column in the Query Builder and choosing "Aggregate" applies MIN, MAX, SUM, AVG or COUNT to it.

The generated SQL updates as you tick and is visible at the bottom of the builder, which is the part worth reading slowly: it is the same statement you would have typed, with the tables and the joins already filled in from the diagram. Run it in the DbSchema SQL Editor with "Execute Query" and the rows come back in the result pane below the query.

Nothing here writes to the database, since a SELECT only reads. The Query Builder is stored in the design model file, so DbSchema asks whether to keep it when you close the builder, and a builder you keep is there the next time you open the model.

Every query on this page runs as written in the SQL Editor of the free DbSchema Community Edition. Download DbSchema at https://dbschema.com/download.html, connect to your database, and start with the GROUP BY example. The visual Query Builder, which writes the grouping list and the aggregates for you, is in the Pro edition.

FAQ

Can SUM and AVG run on a text column?

PostgreSQL 17 lists no text type among the input types for SUM and AVG, so a text column is rejected. MIN and MAX are available for any numeric, string, date/time or enum type, so they read text and dates as well (PostgreSQL 17 documentation).

What does an aggregate function return when no rows match?

COUNT returns 0. SUM, AVG, MIN and MAX return a null value rather than zero when no rows are selected (PostgreSQL 17 documentation).