By Matthew Rathbone · November 13, 2026

The SQL GROUP BY clause puts rows that share a value into groups and returns one row per group. On its own that is not very useful. Paired with an aggregate function such as COUNT, SUM, AVG, MIN or MAX, it answers questions like "how many orders per region?" or "how much has each customer spent?"

SELECT column1, AGGREGATE(column2)
FROM table_name
WHERE condition          -- optional: filter rows first
GROUP BY column1
HAVING condition         -- optional: filter groups after
ORDER BY column1;        -- optional: sort the result

Jump to: how it works · sample data · examples · multiple columns · HAVING · the "must appear in GROUP BY" error · NULLs · order of execution · ROLLUP subtotals · vs DISTINCT and PARTITION BY · differences by database · mistakes · FAQ

What does GROUP BY do in SQL?

Picture the table as a stack of index cards. GROUP BY region sorts the cards into piles, one pile per region. Then every aggregate in the SELECT runs once per pile: COUNT(*) counts the cards in each pile, SUM(amount) adds up the amounts in each pile, and so on. You get back one row per pile.

That leads to the one rule that explains most GROUP BY errors: every column in the SELECT list must either be in the GROUP BY or be inside an aggregate function. A pile has one region, so you can show the region. A pile has many amounts, so you can only show something computed from all of them, such as their sum. More on this in the error section.

Sample data

Every query below was run on SQLite 3.45 and the output is copied from the real result. The ROLLUP example and the error messages come from PostgreSQL 16. Apart from the date functions (covered in group by month), the queries are standard SQL and run on MySQL, PostgreSQL, SQL Server and Oracle too.

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, region TEXT, product TEXT,
                     quantity INTEGER, amount NUMERIC, order_date DATE);
INSERT INTO orders VALUES
 (1, 'Anna',  'East',  'Keyboard', 2,  90, '2026-01-05'),
 (2, 'Ben',   'West',  'Mouse',    1,  25, '2026-01-12'),
 (3, 'Anna',  'East',  'Monitor',  1, 210, '2026-01-20'),
 (4, 'Carla', 'West',  'Keyboard', 1,  45, '2026-02-02'),
 (5, 'Dev',   'North', 'Mouse',    3,  75, '2026-02-14'),
 (6, 'Ben',   'West',  'Monitor',  2, 420, '2026-02-21'),
 (7, 'Anna',  'East',  'Mouse',    1,  25, '2026-03-03'),
 (8, 'Erin',  NULL,    'Keyboard', 1,  45, '2026-03-09'),
 (9, 'Carla', 'West',  'Mouse',    2,  50, '2026-03-15'),
 (10,'Dev',   'North', 'Monitor',  1, 210, '2026-03-28');

Ten orders, five customers, three products, and one order (Erin's) with no region.

SQL GROUP BY examples

1. COUNT rows per group

The most common GROUP BY query of all: how many rows are in each group?

SELECT region, COUNT(*) AS orders
FROM orders
GROUP BY region;
region | orders
NULL   | 1
East   | 3
North  | 2
West   | 4

Ten rows went in, four came out. Erin's order has no region, so it lands in its own NULL group (see NULLs).

2. SUM, AVG, MIN and MAX per group

You can use as many aggregates as you like in one query. Each one is calculated separately for every group.

SELECT product,
       COUNT(*)      AS orders,
       SUM(quantity) AS units,
       SUM(amount)   AS revenue,
       AVG(amount)   AS avg_order,
       MIN(amount)   AS smallest,
       MAX(amount)   AS largest
FROM orders
GROUP BY product;
product  | orders | units | revenue | avg_order | smallest | largest
Keyboard | 3      | 4     | 180     | 60.0      | 45       | 90
Monitor  | 3      | 4     | 840     | 280.0     | 210      | 420
Mouse    | 4      | 7     | 175     | 43.75     | 25       | 75

Monitors are only three orders but more than half the revenue. This one query is the start of most sales reports. To round the average, wrap it in ROUND(AVG(amount), 2).

3. Sort the groups with ORDER BY

GROUP BY does not promise any order. Add ORDER BY to rank the groups, and you can sort by the aggregate's alias:

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer
ORDER BY total_spent DESC;
customer | total_spent
Ben      | 445
Anna     | 325
Dev      | 285
Carla    | 95
Erin     | 45

Add LIMIT 3 (or TOP 3 in SQL Server) and you have a "top customers" report.

4. COUNT DISTINCT per group

COUNT(*) counts rows. COUNT(DISTINCT col) counts different values inside each group:

SELECT region,
       COUNT(*)                 AS orders,
       COUNT(DISTINCT customer) AS customers
FROM orders
GROUP BY region;
region | orders | customers
NULL   | 1      | 1
East   | 3      | 1
North  | 2      | 1
West   | 4      | 2

East has three orders but they all came from Anna.

5. GROUP BY month (or year, or day)

To group by a period, group by an expression that cuts the date down to that period. In SQLite that is strftime:

SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;
month   | orders | revenue
2026-01 | 3      | 325
2026-02 | 3      | 540
2026-03 | 4      | 330

Every database has its own date function for this:

DatabaseGroup by month
PostgreSQLGROUP BY date_trunc('month', order_date)
MySQLGROUP BY DATE_FORMAT(order_date, '%Y-%m')
SQL ServerGROUP BY YEAR(order_date), MONTH(order_date)
OracleGROUP BY TRUNC(order_date, 'MM')
SQLiteGROUP BY strftime('%Y-%m', order_date)

Always keep the year in the group. GROUP BY MONTH(order_date) alone adds January 2025 and January 2026 together. There are step-by-step guides for grouping by month in MySQL, by month in T-SQL and by year in T-SQL.

6. GROUP BY a CASE expression

You are not limited to existing columns. Group by any expression, such as a CASE that puts orders into size buckets:

SELECT CASE WHEN amount >= 200 THEN 'Large'
            WHEN amount >= 50  THEN 'Medium'
            ELSE 'Small' END AS order_size,
       COUNT(*) AS orders
FROM orders
GROUP BY order_size
ORDER BY orders DESC;
order_size | orders
Small      | 4
Medium     | 3
Large      | 3

Grouping by the alias order_size works in SQLite, MySQL and PostgreSQL. In SQL Server, repeat the whole CASE expression in the GROUP BY instead (see differences by database).

7. Turn groups into columns (conditional aggregation)

Put a CASE inside the aggregate and each product becomes its own column. This is the portable way to pivot in SQL:

SELECT region,
       SUM(CASE WHEN product = 'Keyboard' THEN amount ELSE 0 END) AS keyboards,
       SUM(CASE WHEN product = 'Mouse'    THEN amount ELSE 0 END) AS mice,
       SUM(CASE WHEN product = 'Monitor'  THEN amount ELSE 0 END) AS monitors
FROM orders
GROUP BY region;
region | keyboards | mice | monitors
NULL   | 45        | 0    | 0
East   | 90        | 25   | 210
North  | 0         | 75   | 210
West   | 45        | 75   | 420

8. GROUP BY with a JOIN

Grouping works the same after a join: the join builds the rows, then GROUP BY sorts them into piles. A typical pattern counts orders per customer from a separate customers table:

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Two details matter here. Use a LEFT JOIN and COUNT(o.id), not COUNT(*), so customers with no orders show 0 instead of 1 (more in how to include zero in a COUNT). And group by the id as well as the name, so two customers who share a name stay separate.

SQL GROUP BY multiple columns

List more than one column and you get one group for every combination of values that appears in the data:

SELECT region, product, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY region, product
ORDER BY region, product;
region | product  | orders | revenue
NULL   | Keyboard | 1      | 45
East   | Keyboard | 1      | 90
East   | Monitor  | 1      | 210
East   | Mouse    | 1      | 25
North  | Monitor  | 1      | 210
North  | Mouse    | 1      | 75
West   | Keyboard | 1      | 45
West   | Monitor  | 1      | 420
West   | Mouse    | 2      | 75

Only combinations that exist are returned. North has no keyboard orders, so there is no North/Keyboard row with a zero. The order of columns in GROUP BY region, product does not change which groups you get, only how you might choose to sort them.

GROUP BY with HAVING

WHERE cannot see aggregates, because it runs before the groups exist. To filter on a count or a sum, use HAVING, which runs after grouping:

SELECT customer, COUNT(*) AS orders
FROM orders
GROUP BY customer
HAVING COUNT(*) >= 2;
customer | orders
Anna     | 3
Ben      | 2
Carla    | 2
Dev      | 2

Erin, with one order, is gone. You can use WHERE and HAVING in the same query, and they do different jobs. Here WHERE drops monitor orders before grouping, and HAVING keeps only customers whose remaining spend is over 60:

SELECT customer, SUM(amount) AS revenue
FROM orders
WHERE product <> 'Monitor'
GROUP BY customer
HAVING SUM(amount) > 60
ORDER BY revenue DESC;
customer | revenue
Anna     | 115
Carla    | 95
Dev      | 75

Rule of thumb: if the condition is about a single row (product <> 'Monitor'), put it in WHERE. If it is about a group (SUM(amount) > 60), put it in HAVING. Filtering rows early with WHERE is also faster, since fewer rows get grouped.

Repeat the aggregate in HAVING rather than using its alias. HAVING n >= 2 works in SQLite and MySQL, but PostgreSQL 16 answers ERROR: column "n" does not exist, SQL Server rejects it too, and Oracle only accepts it from 23ai.

"Must appear in the GROUP BY clause or be used in an aggregate function"

This is the GROUP BY error everyone meets. It happens when the SELECT list has a column that is neither grouped nor aggregated:

SELECT customer, product, SUM(amount)
FROM orders
GROUP BY customer;

Each customer's pile holds several products, so which one should the database show? Most databases refuse to guess:

DatabaseError
PostgreSQLcolumn "orders.product" must appear in the GROUP BY clause or be used in an aggregate function
SQL ServerColumn 'orders.product' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
MySQL 5.7+ (docs)Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ... this is incompatible with sql_mode=only_full_group_by
OracleORA-00979: not a GROUP BY expression
SQLiteNo error. It returns a value from one arbitrary row of each group.

SQLite's silent answer is the dangerous one:

customer | product  | SUM(amount)
Anna     | Keyboard | 325
Ben      | Mouse    | 445
Carla    | Keyboard | 95
Dev      | Mouse    | 285
Erin     | Keyboard | 45

That reads as if Anna spent 325 on keyboards, which is false. There are three ways to fix the query, depending on what you meant:

  • You wanted one row per customer and product: add the column to the group, GROUP BY customer, product.
  • You wanted one value that summarizes the products: aggregate it, for example COUNT(DISTINCT product), MAX(product), or STRING_AGG(product, ', ') (GROUP_CONCAT in MySQL and SQLite).
  • You wanted the full row next to a group total: use a window function instead of GROUP BY (see GROUP BY vs PARTITION BY).

One exception: if you group by a table's primary key, PostgreSQL and MySQL let you select the table's other columns, because each group is a single row. SELECT id, customer, SUM(amount) FROM orders GROUP BY id runs fine on PostgreSQL 16.

How GROUP BY handles NULL

NULL is never equal to anything in a WHERE clause, but GROUP BY treats all NULLs as one group. That is why example 1 returned a single NULL region row for Erin. Two related points:

  • COUNT(*) counts the rows in the NULL group, while COUNT(region) counts non-NULL values, so it returns 0 for that group.
  • To label the group, wrap the column in COALESCE: SELECT COALESCE(region, 'Unknown') AS region ... GROUP BY COALESCE(region, 'Unknown'). To drop it, add WHERE region IS NOT NULL.

Also note what happens with no matching rows. SELECT COUNT(*) FROM orders WHERE product = 'Webcam' returns one row with 0. Add GROUP BY product and it returns no rows at all, because there are no groups to report on.

GROUP BY order of execution

SQL is written in one order and run in another. Knowing the run order explains why WHERE cannot use aggregates and why aliases work in some places but not others:

  1. FROM and JOIN: build the rows
  2. WHERE: filter rows
  3. GROUP BY: sort rows into groups
  4. HAVING: filter groups
  5. SELECT: compute the output columns and aliases
  6. ORDER BY: sort the result (can use aliases, since SELECT already ran)
  7. LIMIT / TOP: cut the result

Because SELECT runs after GROUP BY and HAVING, the standard says those clauses cannot see column aliases. Some databases bend that rule as a convenience, which is the source of most "works on my laptop" GROUP BY bugs.

Subtotals and grand totals with ROLLUP

GROUP BY ROLLUP adds subtotal rows for each level, plus a grand total. It runs in PostgreSQL, SQL Server, Oracle and Snowflake (MySQL writes it GROUP BY region, product WITH ROLLUP, and SQLite does not support it). Output from PostgreSQL 16:

SELECT region, product, SUM(amount) AS revenue
FROM orders
WHERE region IS NOT NULL
GROUP BY ROLLUP (region, product)
ORDER BY region, product;
 region | product  | revenue
--------+----------+---------
 East   | Keyboard |      90
 East   | Monitor  |     210
 East   | Mouse    |      25
 East   |          |     325
 North  | Monitor  |     210
 North  | Mouse    |      75
 North  |          |     285
 West   | Keyboard |      45
 West   | Monitor  |     420
 West   | Mouse    |      75
 West   |          |     540
        |          |    1150

The rows with a blank product are region subtotals, and the last row is the grand total. Use GROUPING(product) = 1 to tell a subtotal row apart from a real NULL value. For other combinations of totals, see CUBE and GROUPING SETS in the PostgreSQL docs.

GROUP BY vs DISTINCT vs PARTITION BY

GROUP BY vs DISTINCT

With no aggregates, these two return the same rows:

SELECT DISTINCT region FROM orders;
SELECT region FROM orders GROUP BY region;

Use DISTINCT when you only want the unique values, and GROUP BY when you want to calculate something per value. In our test SQLite returned the GROUP BY version sorted and the DISTINCT version in table order; neither is guaranteed, so add ORDER BY if order matters. GROUP BY is also the usual way to find duplicate rows: GROUP BY email HAVING COUNT(*) > 1.

GROUP BY vs PARTITION BY

GROUP BY collapses each group into one row. PARTITION BY in a window function computes the same aggregate but keeps every row:

SELECT customer, product, amount,
       SUM(amount) OVER (PARTITION BY customer) AS customer_total
FROM orders
WHERE customer IN ('Anna', 'Ben')
ORDER BY customer, id;
customer | product  | amount | customer_total
Anna     | Keyboard | 90     | 325
Anna     | Monitor  | 210    | 325
Anna     | Mouse    | 25     | 325
Ben      | Mouse    | 25     | 445
Ben      | Monitor  | 420    | 445

Reach for PARTITION BY when you want detail and totals side by side, such as each order's share of the customer's spend. The window functions guide covers it in depth.

GROUP BY differences by database

FeatureMySQLPostgreSQLSQL ServerSQLiteOracle
Non-grouped column in SELECTError (ONLY_FULL_GROUP_BY, default since 5.7)ErrorErrorAllowed, arbitrary valueError
Alias in GROUP BYYesYesNoYes23ai and later
Alias in HAVINGYesNoNoYes23ai and later
Position (GROUP BY 1)YesYesNoYes23ai, when enabled
ROLLUPWITH ROLLUPYesYesNoYes
Join strings in a groupGROUP_CONCATSTRING_AGGSTRING_AGGGROUP_CONCATLISTAGG

If a query has to run everywhere, repeat full expressions in GROUP BY and HAVING instead of using aliases or positions, and never select an ungrouped column.

Common mistakes

  1. Selecting a column that is not grouped or aggregated. It errors on most databases and returns misleading data on SQLite. See the fix.
  2. Putting an aggregate in WHERE. WHERE COUNT(*) > 1 is invalid. Move it to HAVING.
  3. Grouping by too much. Adding id or a timestamp to the GROUP BY makes every row its own group, so nothing gets summed.
  4. Grouping by month without the year. January of different years gets merged.
  5. Expecting sorted output. GROUP BY may happen to sort, but only ORDER BY guarantees it.
  6. Using COUNT(*) after a LEFT JOIN. It counts the empty match as 1. Count a column from the right-hand table instead.
  7. Forgetting the NULL group. Missing values become their own group, which can look like a mystery row in a report.
  8. Relying on alias support. A query using HAVING alias that works on MySQL fails on PostgreSQL and SQL Server.

FAQ

What does GROUP BY do in SQL?

It collects rows that have the same value in one or more columns into groups and returns one row per group. Aggregate functions in the SELECT list, such as COUNT or SUM, are then calculated for each group separately.

Can you GROUP BY multiple columns?

Yes. Separate them with commas: GROUP BY region, product. You get one row for each combination of values that exists in the data.

What is the difference between GROUP BY and ORDER BY?

GROUP BY combines rows into groups and changes how many rows you get back. ORDER BY only sorts the rows and never changes the count. They are often used together: group first, then sort the groups.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and cannot use aggregates. HAVING filters groups after grouping and is where conditions like COUNT(*) > 5 go.

Can I use GROUP BY without an aggregate function?

Yes. It then returns the distinct values of the grouped columns, the same as SELECT DISTINCT. DISTINCT states the intent more clearly in that case.

Can I use an aggregate without GROUP BY?

Yes. SELECT COUNT(*) FROM orders treats the whole table as one group and returns a single row.

Why does my query say a column "must appear in the GROUP BY clause"?

Because a column in your SELECT has many values per group and the database does not know which one to show. Add it to the GROUP BY, wrap it in an aggregate, or use a window function. Full explanation.

Does GROUP BY sort the results?

Not reliably. Some databases return groups in sorted order as a side effect of how they group, but the SQL standard does not promise it. Add ORDER BY.

Can I GROUP BY a column alias?

In MySQL, PostgreSQL and SQLite, yes. In SQL Server, no, so repeat the expression. Oracle allows it from version 23ai (see Oracle-Base).

How do I count rows per group?

SELECT col, COUNT(*) FROM table GROUP BY col; For distinct values within each group use COUNT(DISTINCT other_col).

Practice

GROUP BY clicks once you have written a few. The free interactive GROUP BY lesson runs in your browser with no signup, and it builds on the COUNT, SUM and MAX and MIN lessons. After that, the SQL practice problems include grouping questions with solutions, and SQL query examples shows GROUP BY inside bigger reports. GROUP BY questions show up in almost every SQL interview, so it is worth being fluent. Keep the SQL cheat sheet open for the syntax.