List every column in the GROUP BY, separated by commas: SELECT region, year, SUM(amount) FROM sales GROUP BY region, year; returns one row for each distinct (region, year) combination. Every other column in the SELECT list has to go inside an aggregate such as COUNT, SUM or AVG.
GROUP BY region folds the rows into one per region. Add a second column and the groups get finer: GROUP BY region, year makes one group for each pair of values that actually occurs, so "East in 2024" and "East in 2025" become separate rows with their own counts and totals. The examples below use a small sales table of orders by region, year and sales rep. They show the aggregates you can compute per group, how to filter and sort the groups, how GROUP BY relates to DISTINCT, how to add subtotals, and the error PostgreSQL raises when a column is missing from the GROUP BY. The examples run on SQLite; the table in section 6 gives the PostgreSQL, MySQL and SQL Server forms for subtotals. Hit Run on any of them, then edit the query and run it again.
1GROUP BY a, b: one row per combinationRecommended
Name both columns in the SELECT list and in the GROUP BY, and put everything else inside an aggregate. The database sorts the rows into groups where region and year are both equal, then computes COUNT(*) and SUM(amount) once per group. Only combinations that exist in the data appear; there is no row for a pair with no orders.
Output
The eight orders collapse into five groups. East 2024 has two orders totalling 205, West 2025 has three totalling 260, and North only appears for 2025, because it has no 2024 rows. The second query groups by year, region and gets the same five groups with the same totals, just sorted by year first. Without ORDER BY the order of the groups isn't guaranteed in any database, so always add one when the order matters.
2More aggregates per group, and sorting by them
Any aggregate works per combination: COUNT(DISTINCT rep) counts the different reps in each group, MAX finds the largest order, and AVG gives the mean, which ROUND(…, 1) trims to one decimal place. You can also ORDER BY an aggregate's alias to rank the groups, and add LIMIT to keep the top few.
Output
West 2025 has three orders but 2 reps, since Cy sold twice. Its average, 260 / 3, rounds to 86.7, and East 2024 averages 102.5. AVG returns a decimal even for an integer column in SQLite, which is why single-order groups show 150.0. The ranked query puts West 2025 (260) first, then East 2024 and West 2024. For a count of only the rows that match a condition inside each group, see count rows with a condition.
3HAVING filters groups, WHERE filters rows
WHERE runs before grouping and decides which rows go into the groups. HAVING runs after grouping and decides which groups stay, so it is the place for a condition on an aggregate such as COUNT(*) >= 2. A query can use both: the rows are filtered, then grouped, then the groups are filtered.
Output
Only East 2024 and West 2025 have two or more orders. In the second query, WHERE amount >= 80 removes Dee's 60 and Eve's 70 before grouping, so West 2025 drops to 2 orders and 200, and North disappears entirely. HAVING SUM(amount) > 150 then removes East 2025, whose total is exactly 150. An aggregate can't go in WHERE: SQLite stops with misuse of aggregate: SUM(), because the groups don't exist yet when WHERE runs.
4GROUP BY vs DISTINCT
With no aggregates, GROUP BY region, year and SELECT DISTINCT region, year return the same rows: one per distinct combination. Use DISTINCT when you only want the combinations, and GROUP BY when you also want a count or total for each. To count the combinations themselves, wrap the DISTINCT query in a subquery.
Output
Both queries print the same five pairs, and the subquery counts 5. Pick the one that says what you mean. The choice starts to matter once you add an aggregate: DISTINCT can't give you a total per pair, while GROUP BY can. To find the combinations that occur more than once, group and add HAVING COUNT(*) > 1, as in find and delete duplicate rows.
5Subtotals and a grand total with UNION ALL
A report often wants a subtotal per region and a grand total under the per-year rows. PostgreSQL, MySQL and SQL Server do this with ROLLUP (next section), but SQLite has no ROLLUP: it answers no such function: ROLLUP. The portable way is one GROUP BY per level, stacked with UNION ALL, with NULL in the columns a level doesn't group by.
Output
Each region's rows are followed by its subtotal (NULL in the year column): 355 for East, 70 for North and 460 for West, then All with the grand total of 885. The outer query exists for the sort: SQLite only lets the ORDER BY of a bare UNION name result columns (anything else fails with 1st ORDER BY term does not match any column in the result set), while the subquery can be sorted by any expression. region = 'All' and year IS NULL are 0 or 1, which pushes the total rows below the detail rows. Use UNION ALL, not UNION: the levels never repeat a row, so the duplicate check only costs time (see UNION vs UNION ALL).
6Other databases: ROLLUP, CUBE and GROUPING SETS
PostgreSQL, MySQL and SQL Server build the subtotal rows for you. ROLLUP (region, year) produces the same nine rows as the UNION ALL query above, with NULL in place of All on the grand-total row. Every form below was run on PostgreSQL 18, MySQL 8.4 and SQL Server (through Babelfish) against the same sales table. Oracle also supports ROLLUP, CUBE and GROUPING SETS.
| Syntax | Database | Notes |
|---|---|---|
| GROUP BY ROLLUP (region, year) | PostgreSQL, MySQL 8.4, SQL Server | Per-year rows, a subtotal per region and a grand total (9 rows here) |
| GROUP BY region, year WITH ROLLUP | MySQL, SQL Server | Older spelling, same rows. Ran on MySQL; not run for SQL Server: Babelfish rejects WITH ROLLUP |
| GROUP BY GROUPING SETS ((region, year), (region), ()) | PostgreSQL, SQL Server | Lists each level explicitly; () is the grand total. MySQL rejects it |
| GROUP BY CUBE (region, year) | PostgreSQL, SQL Server | Every level, including totals per year across regions (11 rows here). MySQL 8.4 rejected it |
| GROUPING(year) | PostgreSQL, MySQL, SQL Server | 1 on a rolled-up row, 0 on a detail row: tells a subtotal NULL from a real NULL |
| UNION ALL of one GROUP BY per level | All, including SQLite | Section 5; the only form SQLite runs |
7The "must appear in the GROUP BY clause" error
A column in the SELECT list that is neither in the GROUP BY nor inside an aggregate is a "bare" column: a group of three rows has three values for it, and the query doesn't say which one to show. SQLite accepts bare columns anyway, which is why a query can work here and fail on another database. With MAX or MIN, SQLite takes the bare column from the row that holds the maximum or minimum.
Output
SQLite shows Ana for East 2024 because her 120 is the group's maximum, and Cy for West 2025 because of his 110. With SUM or COUNT there is no such row, and the bare column comes from whichever row SQLite happens to read. PostgreSQL rejects the query outright. Running SELECT region, year, rep, SUM(amount) AS total FROM sales GROUP BY region, year; there fails with column "sales.rep" must appear in the GROUP BY clause or be used in an aggregate function. MySQL 8.4 refuses it too, with error 1055: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'user_db.sales.rep' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, and SQL Server and Oracle reject it as well.
The fix depends on what you meant. If you want one row per rep as well, add rep to the GROUP BY. If you want one value per group, aggregate it: MIN(rep), COUNT(DISTINCT rep), or a list of names as in combine rows into one string. If you want the rep who made each group's largest sale, which is what the SQLite query above relies on, use the portable patterns in select the first row in each group.
Frequently asked questions
How do I group by multiple columns in SQL?
List the columns in the GROUP BY, separated by commas, and select them along with your aggregates: SELECT region, year, COUNT(*), SUM(amount) FROM sales GROUP BY region, year;. You get one row for each distinct combination of the columns that occurs in the data.
Does the order of columns in GROUP BY matter?
Not for the result. GROUP BY region, year and GROUP BY year, region make the same groups with the same totals. The order of the output rows is decided by ORDER BY, so add one, such as ORDER BY year, region, whenever the order matters.
What is the difference between GROUP BY and DISTINCT?
Without aggregates there is none in the result: SELECT DISTINCT region, year FROM sales and SELECT region, year FROM sales GROUP BY region, year return the same rows. GROUP BY is what you need as soon as you want a COUNT, SUM or other aggregate for each combination.
Why do I get "must appear in the GROUP BY clause or be used in an aggregate function"?
PostgreSQL raises it when the SELECT list has a column that is neither in the GROUP BY nor inside an aggregate, for example rep in SELECT region, year, rep, SUM(amount) FROM sales GROUP BY region, year. Add the column to the GROUP BY, or wrap it in an aggregate such as MIN(rep). MySQL reports the same problem as error 1055 (only_full_group_by); SQLite accepts the query and picks a value for you.
Can I use a column alias in HAVING?
In SQLite and MySQL, yes: HAVING total > 150 works when total is an alias for SUM(amount). PostgreSQL rejects it with column "total" does not exist, so repeat the expression, HAVING SUM(amount) > 150, which works everywhere.
How do I count the distinct combinations of two columns?
Count the rows of a DISTINCT subquery: SELECT COUNT(*) FROM (SELECT DISTINCT region, year FROM sales) AS t; works in every database. MySQL also accepts COUNT(DISTINCT region, year) and PostgreSQL accepts COUNT(DISTINCT (region, year)), but SQLite rejects the two-argument form with wrong number of arguments to function COUNT().