SQL GROUP BY Statement Explained with Examples

A table with one row per transaction answers "how much altogether?" with a single SUM over the whole table. The question that needs GROUP BY is the same one asked a slice at a time: per customer, per month, per branch. GROUP BY collapses the rows that share a value into one row for that value, and the aggregate functions in the SELECT list do the arithmetic over each group separately.

What GROUP BY does in SQL

Grouping is a two-part statement, and the two parts have to agree. GROUP BY names the columns whose values define a group, so two rows land in the same group when they carry the same value in every one of those columns. The SELECT list then says what to report for each group: the grouping columns themselves, plus aggregate functions such as SUM, COUNT, AVG, MIN and MAX that read all the rows of the group and return one number.

The result has exactly one row per distinct combination of the grouping columns. That is the part worth holding on to, because it explains both the row count you get back and why a column outside the grouping has no single value to show. For the same clause worked through against a second schema, see SQL GROUP BY explained.

Where GROUP BY sits in a SQL statement

A full SELECT statement is written in this clause order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. A statement never starts with GROUP BY. It starts with SELECT, or with another command such as INSERT, UPDATE or DELETE.

The order the engine works in is close to the written one but not identical, and it explains most of the surprises further down this page. PostgreSQL documents the processing order[1]: the FROM list is computed, WHERE throws away the rows that fail its condition, GROUP BY forms the groups, HAVING throws away the groups that fail its condition, and only then are the output expressions of the SELECT list computed, with ORDER BY sorting what is left. WHERE therefore filters rows before any grouping happens, and HAVING filters groups after.

Expressions such as a CASE expression or a NULL function run as part of the SELECT list or the grouping columns, so they reshape values on the way into a group rather than after it.

GROUP BY syntax

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

The clause takes a list of columns separated by commas, in the same style as the SELECT list, and it goes after FROM and after any WHERE clause. A name in the list can be a column, an expression built from columns, or the alias of an output column: PostgreSQL allows an output column's name in GROUP BY and ORDER BY[1], and the MySQL manual shows the same form[2].

GROUP BY on a single column

Every query below runs against one table of five sales:

CREATE TABLE Sales (
    Product VARCHAR(10),
    Amount  INT,
    Region  VARCHAR(10)
);

INSERT INTO Sales VALUES
    ('A', 10, 'North'),
    ('B', 15, 'North'),
    ('A', 20, 'South'),
    ('C', 30, 'East'),
    ('B',  5, 'South');

Grouping by Product turns the five sales into one row per product:

SELECT Product, SUM(Amount) AS TotalAmount
FROM Sales
GROUP BY Product;

Product A sold twice, for 10 and 20, and its group reports 30. Product C sold once, so its group holds a single row and SUM returns that row's amount unchanged.

GROUP BY on multiple columns

Adding a second column to the clause makes the group finer: rows now have to match on both columns to land together.

SELECT Product, Region, SUM(Amount) AS TotalAmount
FROM Sales
GROUP BY Product, Region;

Five rows in, five rows out, because no two sales share both a product and a region. The row count is the giveaway: the more columns you group by, the closer the result gets to the table you started from.

GROUP BY with ORDER BY

Sorting happens after grouping, so ORDER BY sorts the grouped rows and can sort them by an aggregate:

SELECT Product, SUM(Amount) AS TotalAmount
FROM Sales
GROUP BY Product
ORDER BY TotalAmount DESC;

Products A and C both total 30, and nothing in this ORDER BY decides which of the two comes first. Add Product as a second sort key when a tie has to come back the same way every time. ORDER BY takes several columns exactly as GROUP BY does, sorting by the first and using the next to break ties.

GROUP BY vs ORDER BY

The two clauses are often written in the same query, and they do unrelated jobs.

GROUP BY decides which rows exist in the result, ORDER BY decides the sequence they arrive in. A grouped query without ORDER BY hands back the groups in whatever order the engine produced them, and a sorted query without GROUP BY hands back every row of the table. An aggregate function belongs to grouping, but ORDER BY is free to sort by one, which is what the previous query does when it sorts on a total.

GROUP BY with HAVING and column expressions

The HAVING clause is the filter for groups, the way WHERE is the filter for rows. Since it runs after the groups are formed, its condition can test an aggregate:

SELECT Product, SUM(Amount) AS TotalAmount
FROM Sales
GROUP BY Product
HAVING SUM(Amount) > 20;

Product B totals 20, which is not greater than 20, so its group is dropped after being formed. Writing that same condition as WHERE Amount > 20 answers a different question: it throws away individual sales before any grouping happens, and only the sale of 30 survives it, so the result is one row for product C.

The aggregate is written out again in the HAVING clause rather than referred to by its alias, and that is the portable form. PostgreSQL states that an output column's name works in ORDER BY and GROUP BY but not in WHERE or HAVING, where the expression has to be written out[1]. MySQL accepts the alias in HAVING, and its manual shows the form[2].

A grouping column can be an expression rather than a bare column, including a call to a user-defined function, which keeps the aggregation itself simple. A column carrying NULL values groups its NULLs together, into one group of their own.

GROUP BY with JOINs

Grouping reads whatever the FROM clause produced, so a join lets you group by a column that lives in another table. A second table gives each product a category:

CREATE TABLE ProductInfo (
    Product  VARCHAR(10),
    Category VARCHAR(20)
);

INSERT INTO ProductInfo VALUES
    ('A', 'Electronics'),
    ('B', 'Apparel'),
    ('C', 'Groceries');

SELECT Category, SUM(Amount) AS TotalAmount
FROM Sales
JOIN ProductInfo ON Sales.Product = ProductInfo.Product
GROUP BY Category;

The join runs first and hands the grouping five rows, each carrying the category its product belongs to. Since each product here has one category, the totals match the per-product totals; give two products the same category and their sales would land in one group.

GROUP BY with aggregate functions

A group can be reported on by several aggregates at once, and each one reads the same rows:

SELECT Product, COUNT(Product) AS NumberOfSales, AVG(Amount) AS AverageSale
FROM Sales
GROUP BY Product;

COUNT returns how many rows the group holds, AVG divides their total by that count. Product A's two sales of 10 and 20 average 15, and product C's single sale of 30 averages itself.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Validate grouped results in DbSchema

A grouped query is easiest to check in two passes: read the ungrouped rows first, then add the aggregate and see whether the totals still add up to what you saw. DbSchema's Query Builder does both without retyping anything. Tick the columns you want, switch on Group By from the builder's toolbar, then right-click the column to summarize and choose Aggregate, which offers MIN, MAX, SUM, AVG and COUNT. The generated SQL sits at the bottom of the builder and changes as you click.

The Query Builder is saved inside the DbSchema model file, while running it reads the connected database and writes nothing to it. To type the statement yourself instead, DbSchema's SQL Editor runs it and puts the grouped rows in the result pane below. The Query Builder is in the Pro edition; connecting, reverse-engineering the schema and the SQL Editor are in the free Community Edition.

Common mistakes

Leaving a column out of the GROUP BY clause is the first one. Every column in the SELECT list that is not inside an aggregate function belongs in the grouping, because a column outside it has no single value to report for the group. PostgreSQL puts one exception on that rule, for a column that is functionally dependent on the grouped ones. A column is functionally dependent on them when their values fix its value: every row in the group carries the same one, so there is nothing for an aggregate to decide. PostgreSQL recognizes that case when the grouping columns include the primary key of the table the ungrouped column comes from[1].

Using HAVING where WHERE was meant is the second. HAVING runs after the groups exist, so a condition on an individual row's value costs the engine a grouping it then throws away, and a condition that names no aggregate belongs in WHERE.

Practice questions

1. Write a SQL statement to find the total amount of sales for each region.
2. Find the average sale amount for each product category.
3. Count the distinct products sold in each region.
4. List the categories whose total sales are more than 50.

Grouping is the kind of thing you check by watching the row count change. Download DbSchema at https://dbschema.com/download.html, connect it to your database, and build the first of these queries in the Query Builder of the Pro edition, or type it in the SQL Editor, which is in the free Community Edition.

FAQs

Can I use GROUP BY without an aggregate function?

GROUP BY works without one, and the rows it hands back are then the same rows SELECT DISTINCT over those columns returns. The groups are still formed underneath, so HAVING can count them even when the SELECT list holds no aggregate: HAVING COUNT(*) > 1 keeps only the values that occur in more than one row, which is how a grouped query finds duplicates.

Why does my grouped query return more rows than I expected?

Count the columns in the GROUP BY clause first, since each one added to it splits the groups further. Then look at what those columns hold: a timestamp column puts every distinct instant in a group of its own, so grouping by the date part of it is what gives one row per day.

Sources

1. SELECT, PostgreSQL documentation
2. MySQL 8.4 Reference Manual: Problems with Column Aliases