What Is GROUP BY in SQL?

Short answer

• GROUP BY in SQL collects rows that share the same values in the listed columns into groups, and returns one row per group. It is usually used with aggregate functions such as COUNT, SUM, AVG, MIN, and MAX, which compute one value per group.
• The standard clause order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT. GROUP BY comes after WHERE and before HAVING and ORDER BY.
• Every column in the SELECT list must either be named in GROUP BY or appear inside an aggregate function. Selecting an ordinary column that is neither grouped nor aggregated is an error in strict systems, and returns an arbitrary value in SQLite.
• HAVING filters groups after aggregation. WHERE filters single rows before grouping. Use WHERE for row level tests and HAVING for tests on a count, total, or average.
• COUNT(*) counts every row in a group, including rows where a column is NULL. COUNT on a column counts only rows where that column is not NULL. SUM, AVG, MIN, and MAX ignore NULL inputs.
• Rows with NULL in a grouping column form one group together, because NULL values are treated as equal for grouping purposes.
• Without ORDER BY, the order of grouped rows is undefined. Add ORDER BY if the order matters.
• GROUP BY without any aggregate function returns the distinct values of the grouped columns, the same set DISTINCT would return.

If you searched for “group by en sql”, you almost certainly mean the GROUP BY clause in SQL. The word “en” in the banked phrase is a fragment, not a SQL keyword. There is no GROUP BY EN clause. The real clause is GROUP BY, and this guide explains exactly what it does, with every example run against a real database so every result shown is a result the database actually returned.

GROUP BY answers summary questions. A plain SELECT returns one row per matching row in a table. GROUP BY collects rows that share the same values into groups, and returns one row per group, usually with a calculated value for each group, such as a count, a total, or an average. If you want sales per category, orders per customer, or average salary per department, GROUP BY is the clause that does it.

This guide uses one small sales table for every example, so each result can be checked by hand. It covers what GROUP BY does, the syntax in clause order, aggregation with COUNT, SUM, AVG, MIN, and MAX, the difference between COUNT(*) and COUNT on a column, grouping by more than one column, the relationship with HAVING, WHERE used together with HAVING, grouping by an expression, sorting grouped results, NULL behavior, grouping with a join, and the most common mistakes, including the ones that silently return wrong numbers instead of an error.

What GROUP BY Does

Think of a table as a pile of rows. GROUP BY divides that pile into buckets, one bucket per distinct combination of the grouping values, then hands each bucket to the aggregate functions. Each aggregate function looks at the rows in one bucket and returns a single number. The query then returns one row per bucket: the grouping values, plus the aggregate results.

PostgreSQL documentation states the rule plainly: the GROUP BY clause groups together rows in a table that have the same values in all the columns listed, combining each set of rows with common values into one group row. The grouped by columns can be referenced in the select list, because they have a single value in each group. Columns that are not listed in GROUP BY cannot be referenced except inside aggregate expressions.

A concrete example makes this concrete. A table of 17 sales has two categories, Accessories and Monitors. Grouping by category produces exactly two groups, and therefore exactly two result rows. The number of result rows always equals the number of groups, not the number of input rows. That is the first thing to check when a grouped query surprises you: count the groups, and you have counted the result rows.

Grouping also changes what a column means in the result. Before grouping, the amount column is one sale. After grouping by category, SUM(amount) is the total sales of a whole category, and AVG(amount) is the average sale inside that category. The same column name now describes a group, not a row. Most GROUP BY mistakes come from forgetting that shift.

Read the Syntax in Clause Order

The standard form used in this guide is:

SELECT category, COUNT(*) AS sale_count, SUM(amount) AS total_sales
FROM sales
WHERE sale_date >= '2026-01-10'
GROUP BY category
HAVING SUM(amount) > 100
ORDER BY total_sales DESC;

Read the clauses in this order. SELECT names the columns to return. FROM names the table. WHERE filters single rows. GROUP BY forms the groups. HAVING filters the groups. ORDER BY sorts the surviving groups. LIMIT restricts how many are returned.

Clause order matters, and it is fixed. MySQL documentation states that clauses must be given in exactly the order shown, so GROUP BY must come after WHERE and before HAVING and ORDER BY. Writing HAVING before GROUP BY is a syntax error. The same order holds in PostgreSQL and in SQLite, where the clauses run SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT.

Written order differs from logical order. You write SELECT first, but the database filters with WHERE, groups, computes aggregates, and applies HAVING before finalizing the SELECT list and sort. That is why an aggregate function cannot appear in WHERE: at the row filtering stage, the group total does not exist yet. It is also why a SELECT alias is safest when repeated as a full expression in HAVING, a point the mistakes section returns to.

The Sample Table Used Throughout This Guide

Every example uses the same table, named sales, with 17 rows. It is small enough to check by hand and large enough to show real grouping behavior, including a NULL region and several NULL discounts. The table was created and queried in SQLite 3.45.1, and every result table in this guide is the output that database actually returned. Amounts are in dollars.

Check the totals before going further. The table has 17 rows. Accessories has 13 rows and Monitors has 4. Total sales are 1,540.00 dollars, and the average sale is 90.59 dollars. Those three numbers anchor every example that follows: any grouped query over the whole table must account for all 17 rows and all 1,540.00 dollars, unless a WHERE clause has removed some rows first.

A second table, named products, appears in one later section, where grouping follows a join. It lists each product once, with its list price and supplier. The products table has 9 rows: Laptop Stand, Wireless Mouse, USB-C Hub, and Desk Lamp from Acme Supply; Mechanical Keyboard, Webcam HD, and Cable Organizer from CoreTech; Monitor 27in and Monitor 32in from Bright Goods.

First Grouped Query: Counting Rows per Category

The simplest grouped query counts the rows in each group:

SELECT category, COUNT(*) AS sale_count
FROM sales
GROUP BY category
ORDER BY category;

Result:

Two groups, two rows. COUNT(*) counts every row in the group, and the two counts add to 17, the whole table. That addition is a useful habit: when a grouped count does not add to the expected total, either a WHERE clause removed rows, or a NULL grouping value is hiding in its own group, as the NULL section shows.

Note the ORDER BY. Without it, the database may return Accessories first or Monitors first, and the order is not guaranteed. SQLite documentation states that if a SELECT statement has no ORDER BY clause, the order in which rows are returned is undefined. Grouping does not sort. If the order matters to a reader or to a downstream step, write ORDER BY explicitly.

Aggregation With COUNT, SUM, AVG, MIN, and MAX

COUNT is only the start. The same grouping can carry several aggregates at once, one per group:

SELECT category, COUNT(*) AS sale_count, SUM(amount) AS total_sales,
       ROUND(AVG(amount), 2) AS average_sale,
       MIN(amount) AS smallest_sale, MAX(amount) AS largest_sale
FROM sales
GROUP BY category
ORDER BY category;

Result:

Check the arithmetic by hand. Accessories: 13 sales total 570.00 dollars, and 570.00 divided by 13 equals 43.85 rounded to two decimal places. The smallest Accessories sale is the 12.00 dollar Cable Organizer, and the largest is the 89.00 dollar Mechanical Keyboard. Monitors: 4 sales total 970.00 dollars, and 970.00 divided by 4 equals 242.50. The smallest Monitor sale is 220.00 dollars and the largest is the 310.00 dollar Monitor 32in.

Two observations matter here. First, the category with more sales has lower total sales. Monitors has only 4 sales but 970.00 dollars of revenue, against 570.00 dollars from 13 Accessories sales. A count and a sum answer different questions, and grouping makes both visible at once. Second, AVG is computed per group, not over the whole table. The overall average is 90.59 dollars, which appears in neither group row. Averaging the two group averages would give a wrong overall figure, because the groups have different sizes. If you need the overall average, compute it in a separate query with no GROUP BY, as the grand total section shows.

ROUND appears here because AVG returns a floating point value, and an average such as 43.846153… is rarely what a report should display. SQLite documentation states that the result of avg() is always a floating point value whenever there is at least one non-NULL input, even if all inputs are integers. Rounding in the query keeps the display clean, but remember that the rounded value is a display value: a HAVING test against a rounded average can behave differently from a test against the exact average, so keep thresholds away from repeating decimals.

COUNT(*) Versus COUNT on a Column: What NULL Does

The most misunderstood aggregate distinction is COUNT() against COUNT on a column. COUNT() counts rows. COUNT on a column counts rows where that column is not NULL. The discount column in the sample table has NULLs, so the two counts differ:

SELECT category, COUNT(*) AS rows_in_group,
       COUNT(discount) AS rows_with_discount
FROM sales
GROUP BY category
ORDER BY category;

Result:

Accessories has 13 rows but only 5 have a discount recorded. Monitors has 4 rows but only 1 has a discount. The other rows are not missing sales. They are sales with no discount, stored as NULL. COUNT(discount) skips them, because SQLite documentation states that count(X) returns a count of the number of times that X is not NULL in a group, while count(*) returns the total number of rows in the group.

The same NULL skipping rule applies to the other aggregates. SUM and AVG ignore NULL inputs. That is visible in the discount totals:

SELECT category, SUM(discount) AS total_discount,
       AVG(discount) AS average_discount
FROM sales
GROUP BY category
ORDER BY category;

Result:

Accessories discounts are 5.00, 2.50, 10.00, 5.00, and 3.00, which sum to 25.50. The average is 25.50 divided by 5, the number of non-NULL discounts, giving 5.10. It is not 25.50 divided by 13. If you need the average discount across all sales, including the ones with no discount, that is a different question, and it needs a different expression, such as treating NULL as zero before averaging. Monitors has one discount of 20.00, so its sum and its average are both 20.00.

One more boundary case matters. If every input to SUM in a group is NULL, SUM returns NULL, not zero. SQLite documentation states this explicitly, and notes that the SQL standard requires it. AVG likewise returns NULL when there are no non-NULL inputs. A report that expects zero must handle that case, for example with COALESCE, rather than assuming an empty sum is zero.

Grouping by Multiple Columns

GROUP BY accepts more than one column. Each distinct combination of the listed values forms its own group. Grouping by category and region splits the table into category and region pairs:

SELECT category, region, COUNT(*) AS sale_count,
       SUM(amount) AS total_sales
FROM sales
GROUP BY category, region
ORDER BY category, region;

Result:

Nine groups, nine rows. The counts add to 17, and the totals add to 1,540.00, so no rows were lost. PostgreSQL documentation notes that the order in which the columns are listed does not matter for the grouping itself: grouping by region and category would produce the same nine groups. The order of the columns in the list only affects how you read the result, not which rows belong together.

Multi-column grouping is how summary reports get their rows. Category alone gives a two row summary. Category and region gives a nine row summary. Adding a third column, such as the sale month, would split the groups further, and the groups would get smaller and more numerous. Each extra grouping column multiplies the detail and divides the group sizes, so the art of a grouped report is choosing the level of detail that answers the question without drowning it in rows.

Note the first row: Accessories with a NULL region. The Cable Organizer sale has no region recorded, and it forms a group of its own. NULL handling in grouping gets its own section below, but the rule is already visible here: a NULL grouping value is a group, not an error, and not a row that disappears.

The HAVING Relationship

GROUP BY forms groups. HAVING filters them. That is the whole relationship, and it is the reason the two clauses are almost always taught together. If you want categories whose total sales pass a threshold, write the threshold in HAVING:

SELECT category, COUNT(*) AS sale_count, SUM(amount) AS total_sales
FROM sales
GROUP BY category
HAVING SUM(amount) > 300
ORDER BY total_sales DESC;

Result:

Both categories pass the 300 dollar test, so both remain. Raise the threshold to 600, and only Monitors survives. The filter is applied to the group values, after aggregation, which is why HAVING can test SUM, COUNT, and AVG, and WHERE cannot.

HAVING can also test a count, which is a common use. Products sold more than once:

SELECT product, COUNT(*) AS sale_count
FROM sales
GROUP BY product
HAVING COUNT(*) > 1
ORDER BY product;

Result:

Seven products sold more than once. Monitor 32in and Cable Organizer each sold once, so they fail the test and disappear. A group that fails HAVING disappears completely. It is not shown with a zero. That is a deliberate feature, not a gap: HAVING keeps groups, it does not annotate them.

The full treatment of HAVING, including HAVING without GROUP BY, HAVING with a join, and the clause order in detail, is covered in the companion guide to HAVING in SQL. Read that guide next if your question is really about filtering groups rather than forming them.

WHERE Versus HAVING, and Using Both Together

WHERE and HAVING look similar because both filter, but they filter different things at different stages. WHERE filters single rows before grouping. HAVING filters whole groups after grouping and aggregation. A test on a single row value, such as a date or a status, belongs in WHERE. A test on a group value, such as a total or a count, belongs in HAVING.

The difference is visible in the numbers. This query keeps only sales above 50 dollars, then groups what remains:

SELECT category, COUNT(*) AS sale_count, SUM(amount) AS total_sales
FROM sales
WHERE amount > 50
GROUP BY category
ORDER BY category;

Result:

Accessories falls from 13 sales and 570.00 dollars to 4 sales and 288.00 dollars, because nine Accessories sales are 50 dollars or less. Monitors is unchanged, because every Monitor sale is above 50 dollars. WHERE removed rows before the groups were formed, so the groups and their totals changed.

Now combine both filters. Keep sales from 10 January 2026 onward, group by region, and keep only regions whose total passes 100 dollars:

SELECT region, COUNT(*) AS sale_count, SUM(amount) AS total_sales
FROM sales
WHERE sale_date >= '2026-01-10'
GROUP BY region
HAVING SUM(amount) > 100
ORDER BY total_sales DESC;

Result:

Three regions survive. North fails the HAVING test: its sales from 10 January onward are the 25.00 dollar Wireless Mouse and the 55.00 dollar Webcam HD, totaling 80.00 dollars, below the 100 dollar threshold. The NULL region also fails, with a single 12.00 dollar sale. Read the query in stage order to see why: WHERE first removes every sale before 10 January, then GROUP BY forms region groups from the survivors, then HAVING removes the groups whose totals fail the test.

A common error is putting an aggregate in WHERE. A test such as WHERE COUNT(*) > 1 fails, because the count does not exist at the row filtering stage. The database reports an error, and the fix is to move the test to HAVING. The reverse error is quieter and worse: putting a row level test in HAVING can run without an error in some systems, and return a misleading result, because HAVING evaluates against one arbitrarily chosen row of the group. Put row tests in WHERE, always.

GROUP BY Without an Aggregate: Distinct Values

GROUP BY does not require an aggregate function. Without one, it returns the distinct values of the grouped columns, which is the same set DISTINCT would return:

SELECT region
FROM sales
GROUP BY region
ORDER BY region;

Result:

Five rows: the four named regions plus the NULL group. PostgreSQL documentation makes the same point: grouping without aggregate expressions effectively calculates the set of distinct values in a column, and this can also be achieved using the DISTINCT clause. Choose whichever reads better for your query. DISTINCT states the intent directly when you only want unique values. GROUP BY states the intent directly when you are about to add aggregates, and the grouping is the reason the values are unique.

Grouping by an Expression

The GROUP BY list does not have to be plain column names. It can hold expressions, which lets you group by a computed value such as a month, a year, or a price band. Grouping sales by month:

SELECT strftime('%Y-%m', sale_date) AS sale_month,
       COUNT(*) AS sale_count, SUM(amount) AS total_sales
FROM sales
GROUP BY sale_month
ORDER BY sale_month;

Result:

Twelve January sales total 1,193.00 dollars, and five February sales total 347.00 dollars. The two groups add to 17 rows and 1,540.00 dollars, so the split is complete. The month values come from the sale_date column, not from a column of their own, which shows that grouping works on any expression that returns the same value for rows that belong together.

A CASE expression groups rows into bands you define yourself. Grouping by price band:

SELECT CASE
         WHEN amount < 50 THEN 'under 50'
         WHEN amount < 100 THEN '50 to 99'
         ELSE '100 or more'
       END AS price_band,
       COUNT(*) AS sale_count, SUM(amount) AS total_sales
FROM sales
GROUP BY price_band
ORDER BY price_band;

Result:

Nine sales under 50 dollars total 282.00 dollars. Four sales from 50 to 99 dollars total 288.00 dollars. Four sales of 100 dollars or more total 970.00 dollars, all of them Monitors. The CASE expression is the grouping key, so rows land in bands by their amount, not by any stored column. The companion guide to SQL CASE expressions covers CASE in full, including CASE in the SELECT list and in ORDER BY.

One caution applies to grouping by expression: the expression in GROUP BY must match the expression in the SELECT list, or the grouping key will not be the column you display. In MySQL, an alias such as sale_month can be used in GROUP BY, because MySQL permits aliases there. In strict standard SQL, aliases are not permitted in GROUP BY, so the portable form repeats the full expression. When in doubt, repeat the expression.

Sorting Grouped Results and Taking the Top Groups

Grouped rows have no natural order, so ORDER BY does the sorting, and it can sort by an aggregate. The three best selling products by total sales:

SELECT product, COUNT(*) AS sale_count, SUM(amount) AS total_sales
FROM sales
GROUP BY product
ORDER BY total_sales DESC
LIMIT 3;

Result:

Monitor 27in leads with 660.00 dollars across 3 sales. Monitor 32in is second with a single 310.00 dollar sale, which shows why sorting by total and sorting by count give different answers: the most frequent product is not always the highest revenue product. ORDER BY total_sales DESC sorts by the aggregate, and LIMIT 3 keeps the top three groups. LIMIT runs after ORDER BY, so the three rows returned are genuinely the top three, not three arbitrary groups.

Sorting by an aggregate in ORDER BY is safe and standard. Sorting by a non-grouped, non-aggregated column is not, because such a column has no single value inside a group. Keep ORDER BY to grouping columns and aggregates, and grouped queries stay deterministic.

The Grand Total: Aggregates With No GROUP BY

A query with aggregate functions and no GROUP BY clause still groups. It forms exactly one group from all the rows, and returns exactly one row:

SELECT COUNT(*) AS sale_count, SUM(amount) AS total_sales,
       ROUND(AVG(amount), 2) AS average_sale
FROM sales;

Result:

PostgreSQL documentation states the same rule: if a query contains aggregate function calls but no GROUP BY clause, grouping still occurs, and the result is a single group row. SQLite documentation adds that an aggregate query without a GROUP BY clause always returns exactly one row of data, even if there are zero rows of input data. That last detail matters: COUNT(*) over an empty result returns 0, not zero rows, because the single group still exists. If a query with GROUP BY finds no rows, it returns no groups and no rows, which is a different outcome. Keep the two cases apart when a report must show a zero instead of an empty result.

COUNT(DISTINCT …): Counting Unique Values per Group

COUNT(DISTINCT column) counts the distinct non-NULL values of a column inside each group, instead of counting rows. Counting distinct products per category:

SELECT category, COUNT(DISTINCT product) AS distinct_products
FROM sales
GROUP BY category
ORDER BY category;

Result:

Accessories has 13 sales but only 7 distinct products, because several products sold more than once. Monitors has 4 sales across 2 distinct products. The companion guide to COUNT of COUNT in SQL covers nested counting patterns in more depth, including counting the results of a count, which is a different technique from COUNT(DISTINCT …).

COUNT(DISTINCT …) is the right tool when the question is about variety rather than volume: how many different products, customers, or regions appear in each group. COUNT(*) answers how many rows. The two numbers diverge whenever a value repeats, which is most of the time in real data.

NULL Values in Grouping Columns

NULL in a grouping column does not cause an error, and it does not make the row vanish. Rows with NULL in the grouping column form one group together, because NULL values are treated as equal for grouping purposes. SQLite documentation states this directly: for the purposes of grouping rows, NULL values are considered equal.

Grouping the sales table by region shows the NULL group plainly:

SELECT region, COUNT(*) AS sale_count, SUM(amount) AS total_sales
FROM sales
GROUP BY region
ORDER BY region;

Result:

The NULL group holds the single Cable Organizer sale, worth 12.00 dollars, with no region recorded. The five groups account for all 17 rows and all 1,540.00 dollars. If a report must exclude unassigned rows, filter them out with WHERE region IS NOT NULL before grouping. If a report must label them, use COALESCE(region, ‘Unassigned’) as the grouping expression, which turns the NULL group into a readable label.

Do not confuse this with NULL inside aggregates. A NULL grouping value creates a group. A NULL aggregate input is skipped by the aggregate. Both rules come from the same idea: NULL means unknown, and grouping treats two unknowns as the same bucket, while aggregation refuses to count an unknown as a value.

GROUP BY With a Join

Grouping often follows a join, because the grouping column may live in a different table from the values being aggregated. Grouping sales by supplier needs the products table, which holds the supplier for each product:

SELECT p.supplier, COUNT(*) AS sale_count, SUM(s.amount) AS total_sales
FROM sales s
JOIN products p ON s.product = p.product
GROUP BY p.supplier
ORDER BY total_sales DESC;

Result:

Bright Goods leads with 970.00 dollars from 4 sales, all of them Monitors. CoreTech has 5 sales totaling 300.00 dollars. Acme Supply has the most sales, 8, but the lowest total, 270.00 dollars, because its products are the cheapest. The join runs first, producing one combined row per sale with its supplier attached, and then GROUP BY collects those combined rows by supplier. WHERE filters would apply to the joined rows before grouping, in the same stage order as a single table query.

The join itself is an inner join, which keeps only sales whose product appears in the products table. Every product in the sales table does appear there, so no sale is lost. If a sale referenced a product missing from the products table, an inner join would drop it silently, and the grouped totals would no longer add to 1,540.00 dollars. When totals must be complete, check the join type and the match rate before trusting the grouped result. The companion guide to inner joins in SQL explains the join types, and the companion guide to left versus right joins covers the outer join forms; note that the left versus right join guide is part of the same wave as this article and is not yet live, so confirm it is live before following that link.

Advanced Grouping: ROLLUP, CUBE, and GROUPING SETS

Plain GROUP BY produces one level of summary. ROLLUP, CUBE, and GROUPING SETS produce several levels in one query, by grouping the same rows under several different grouping sets and stacking the results.

PostgreSQL documentation defines the three forms. GROUPING SETS lists the groupings explicitly, so GROUP BY GROUPING SETS ((brand), (size), ()) returns subtotals by brand, subtotals by size, and a grand total, in one result. ROLLUP is shorthand for a hierarchy: ROLLUP (a, b, c) produces groupings (a, b, c), (a, b), (a), and the empty grouping, which is the grand total. That fits hierarchical data such as totals by region, then by region and category, then the overall total. CUBE produces every possible subset of the listed columns, so CUBE (a, b, c) produces all eight combinations, from the full detail down to the grand total. Rows for a grouping set that omits a column show NULL in that column, which marks the row as a subtotal rather than a detail row.

Two practical notes. First, SQLite does not support ROLLUP, CUBE, or GROUPING SETS, so the examples in this guide do not run them; the definitions above come from the PostgreSQL documentation, which documents all three. Second, Microsoft SQL Server documents the same three forms, and adds GROUP BY () as the grand total grouping on its own. Use these forms when a report needs subtotals and a grand total in a single result. For a single level of summary, plain GROUP BY is clearer and easier to read.

Common Mistakes

Mistake 1: selecting a column that is neither grouped nor aggregated. Wrong: SELECT region, product, COUNT(*) FROM sales GROUP BY region, expecting one product per region. Right: group by every non-aggregated column you select, or aggregate it. In strict systems such as PostgreSQL and MySQL with ONLY_FULL_GROUP_BY enabled, the wrong form is an error. MySQL reports error 1055, stating that the column is not functionally dependent on the columns in the GROUP BY clause. In SQLite, the wrong form runs, and the bare column takes its value from an arbitrary row of the group. The run below shows it:

SELECT region, product, COUNT(*) AS sale_count
FROM sales
GROUP BY region
ORDER BY region;

Result:

East has 5 sales across 4 products, but the result shows only Mechanical Keyboard, because SQLite picked one arbitrary row of the group for the bare product column. The count is correct and the product is misleading. SQLite documentation warns that the value for a bare column is usually undefined, and that most other SQL database engines disallow bare columns. Never rely on a bare column. Add the column to GROUP BY, or wrap it in an aggregate such as MIN or MAX.

Mistake 2: putting an aggregate in WHERE. Wrong: WHERE SUM(amount) > 300. Right: GROUP BY category HAVING SUM(amount) > 300. WHERE runs before grouping, so the total does not exist yet. The database reports an error, which is the kind outcome: the mistake is caught at once.

Mistake 3: putting a row level test in HAVING. Wrong: HAVING amount > 50, expecting it to remove cheap sales from the totals. Right: WHERE amount > 50, then GROUP BY, then HAVING for group tests. A row test in HAVING evaluates against one arbitrarily chosen row of the group in SQLite, so the result is unpredictable. The WHERE then HAVING example above shows the correct stage order.

Mistake 4: confusing COUNT() with COUNT on a column. Wrong: using COUNT(discount) to count sales. Right: COUNT() counts rows, COUNT(discount) counts rows with a discount recorded. In this dataset the two differ by 8 in Accessories and by 3 in Monitors. The discount section above shows the exact numbers.

Mistake 5: expecting a zero row for a missing group. Wrong expectation: grouping sales by region will list a region with no sales and a count of zero. Right: no group exists for a value that never appears in the grouped rows. A region with no sales produces no group, just as a customer with no orders produces no group. To list every region, start from a regions table with a left join to sales, and count the sale identifier so the unmatched region counts as zero.

Mistake 6: forgetting that WHERE runs before aggregation. Wrong expectation: filtering on a date in HAVING will still let earlier sales contribute to the total. Right: put the date test in WHERE when earlier sales must not contribute. The combined WHERE and HAVING example shows North falling out of the result entirely, because its surviving total is only 80.00 dollars.

Mistake 7: relying on a SELECT alias in HAVING for portable code. Wrong: HAVING total_sales > 300, where total_sales is a SELECT alias for SUM(amount). Right: HAVING SUM(amount) > 300. MySQL documentation permits an alias in HAVING as a MySQL extension, but other systems are stricter, so repeating the expression works more widely.

Mistake 8: assuming grouped results come back sorted. Wrong: relying on GROUP BY to return groups in alphabetical or size order. Right: add ORDER BY, because without it the order of grouped rows is undefined. The category count example returns Accessories before Monitors only because ORDER BY category says so.

Mistake 9: grouping by the wrong level of detail. Wrong: grouping by product when the question asks for totals by category, then summing the product rows by hand. Right: group by the column the question names. Each extra grouping column splits groups further, and each missing grouping column merges groups that should stay apart. State the question first, then choose the grouping columns from it.

Mistake 10: using GROUP BY 1, the positional form, without knowing it is positional. The query GROUP BY 1 groups by the first SELECT column, which is category in the examples here, and it returns the same result as GROUP BY category. It works in SQLite, PostgreSQL, and MySQL, but it breaks silently when someone reorders the SELECT list, because the number then points at a different column. Prefer the column name. The positional form is a shorthand for interactive use, not for code that other people will maintain.

Dialect Differences: PostgreSQL, MySQL, and SQLite

The core behavior of GROUP BY is the same across the three systems covered here: it groups rows with equal grouping values, returns one row per group, and pairs with aggregates that compute one value per group. The differences are at the edges.

MySQL deserves a closer look, because its default behavior changed over time and still catches people out. MySQL 8.4 documentation states that ONLY_FULL_GROUP_BY is enabled by default, and that it rejects queries where the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor functionally dependent on them. Older MySQL setups often had the mode disabled, which allowed bare columns and returned an arbitrary value per group, the same trap SQLite still permits. If a query that worked on an old MySQL server fails on a new one with error 1055, the cause is usually this mode, and the fix is to write the strict form: group by the column or aggregate it. MySQL also offers ANY_VALUE() as a way to mark a column as deliberately arbitrary, which keeps the mode enabled while stating the intent.

The practical lesson is to write the strict, explicit form always: group by every non-aggregated column you select, repeat aggregate expressions in HAVING, put single row filters in WHERE, group by column names rather than positions, and add ORDER BY whenever the order matters. Code written that way runs unchanged across PostgreSQL, MySQL, and SQLite.

When to Use GROUP BY

GROUP BY is the right tool whenever the question asks for a summary by category. Sales per category, orders per customer, average salary per department, error counts per day, and distinct values per group are all GROUP BY questions. The pattern is the same in each case: one grouping column, one or more aggregates, and optionally HAVING to keep only the groups that matter.

It is the wrong tool when the question asks about individual rows. Listing the ten largest sales needs ORDER BY and LIMIT, not GROUP BY. Finding sales above a threshold needs WHERE, not HAVING. Grouping adds value only when rows must be combined. If no combination is needed, a plain SELECT is clearer and faster.

GROUP BY also belongs early in query design, not as an afterthought. Decide the grouping columns from the question before writing the query, because they determine the shape of the result: one row per group, and every other column either a grouping column or an aggregate. Queries designed the other way round, with columns added first and grouping fitted afterward, are the ones that produce bare column errors and arbitrary values.

For readers building data skills, GROUP BY pairs with the wider SQL toolkit. The companion guide to SQL for data analysts places grouping in a working analyst workflow, and the companion guide to equal and not equal in SQL covers the comparison operators that appear in WHERE clauses before grouping. The companion guide to SQL order of operations explains the full clause evaluation order; note that it is part of the same wave as this article and is not yet live, so confirm it is live before following that link.

FAQ

What is GROUP BY in SQL?

GROUP BY is a clause that collects rows sharing the same values in the listed columns into groups, and returns one row per group. It is usually used with aggregate functions such as COUNT, SUM, AVG, MIN, and MAX, which compute one value per group. A query grouped by category returns one row per category, with the count, total, or average for that category.

What does GROUP BY do without an aggregate function?

Without an aggregate function, GROUP BY returns the distinct values of the grouped columns. Grouping a sales table by region returns one row per region, the same set SELECT DISTINCT region would return. The grouping is still useful, because it states that the values are unique by construction, and aggregates can be added later without restructuring the query.

What is the difference between GROUP BY and ORDER BY?

GROUP BY collects rows into groups and returns one row per group, usually with aggregates. ORDER BY sorts the rows that a query returns, without changing how many rows there are or what they contain. A grouped query often uses both: GROUP BY forms the groups, and ORDER BY sorts the group rows, for example by total sales from highest to lowest.

Can I use GROUP BY with more than one column?

Yes. GROUP BY category, region forms one group per distinct combination of category and region. Each extra grouping column splits the groups further, so the result has more rows and smaller groups. The order of the columns in the list does not change which rows belong together.

Why does my GROUP BY query return the wrong total?

The usual causes are a WHERE clause that removed rows before grouping, a join that dropped unmatched rows, NULL values being skipped by an aggregate, or COUNT on a column being used where COUNT(*) was meant. Check the row count first: grouped counts should add to the expected total, and grouped sums should add to the expected grand total. In the sample table used here, grouped counts add to 17 and grouped totals add to 1,540.00 dollars.

How do I filter groups in SQL?

Use HAVING after GROUP BY. HAVING tests group values such as COUNT, SUM, or AVG, and removes whole groups that fail the test. WHERE filters single rows before grouping and cannot test aggregates. The companion guide to HAVING in SQL covers group filtering in full.

Does GROUP BY ignore NULL values?

No, not in the grouping column. Rows with NULL in a grouping column form one group together, because NULL values are treated as equal for grouping. Inside aggregates, NULL inputs are ignored: COUNT on a column skips NULL rows, and SUM and AVG skip NULL values. COUNT(*) is the exception: it counts every row in the group, including rows where a column is NULL.

Can I use GROUP BY with WHERE?

Yes, and most real grouped queries use both. WHERE first removes single rows, then GROUP BY forms groups from the survivors, then HAVING removes groups that fail the group level test. Put row level tests in WHERE and group level tests in HAVING.

Why does my query fail with error 1055 in MySQL?

Error 1055 means the query selects a nonaggregated column that is not in the GROUP BY clause and is not functionally dependent on the grouping columns, while ONLY_FULL_GROUP_BY is enabled. MySQL enables that mode by default. Fix it by adding the column to GROUP BY, wrapping it in an aggregate, or using ANY_VALUE() to mark it as deliberately arbitrary.

Does GROUP BY sort the results?

No. Grouping does not sort. Without ORDER BY, the order of grouped rows is undefined, and it can change between runs. Add ORDER BY if the order matters, for example ORDER BY total_sales DESC to list the largest groups first.

Can I group by a column that is not in the SELECT list?

Yes. The GROUP BY expressions do not have to appear in the result. SQLite documentation states this explicitly, and the same holds in PostgreSQL and MySQL. Grouping by a column you do not display is common when the grouping is a means to an end, such as grouping by month to compute monthly totals without showing the month.

What is the difference between GROUP BY and DISTINCT?

DISTINCT removes duplicate rows from a result. GROUP BY collects rows into groups and returns one row per group, usually with aggregates. Without aggregates, GROUP BY returns the distinct values of the grouped columns, which is the same set DISTINCT would return. Use DISTINCT when you only want unique values, and GROUP BY when you want summaries per group.

Sources & references

• PostgreSQL Documentation, Table Expressions, GROUP BY and HAVING Clauses section: GROUP BY groups rows with the same values in all listed columns into one group row; columns not listed in GROUP BY cannot be referenced except in aggregate expressions; grouping without aggregates calculates the set of distinct values; HAVING eliminates groups; aggregates without GROUP BY produce a single group row. https://www.postgresql.org/docs/current/queries-table-expressions.html
• PostgreSQL Documentation, Table Expressions, GROUPING SETS, CUBE, and ROLLUP section: GROUPING SETS groups by each listed set separately; ROLLUP produces a list and all its prefixes including the empty list; CUBE produces all possible subsets; references to grouping columns are replaced by null values in rows for grouping sets where they do not appear. https://www.postgresql.org/docs/current/queries-table-expressions.html
• MySQL 8.4 Reference Manual, MySQL Handling of GROUP BY: ONLY_FULL_GROUP_BY is enabled by default and rejects nonaggregated columns that are neither named in GROUP BY nor functionally dependent on grouping columns; error 1055; ANY_VALUE() as the way to mark a column as deliberately arbitrary; alias permitted in HAVING as a MySQL extension; noncolumn expressions permitted in GROUP BY as a MySQL extension. https://dev.mysql.com/doc/refman/8.4/en/group-by-handling.html
• SQLite Documentation, SELECT: aggregate query rules, NULL values considered equal for grouping, bare columns taking a value from an arbitrarily selected row, MIN and MAX special handling of bare columns, aggregate without GROUP BY always returning exactly one row, and ORDER BY undefined without an ORDER BY clause. https://sqlite.org/lang_select.html
• SQLite Documentation, Built-in Aggregate Functions: count(X) counts non-NULL values, count(*) counts all rows, avg() returns a floating point value and NULL when there are no non-NULL inputs, sum() returns NULL when there are no non-NULL inputs, and count(distinct X) counts distinct values. https://sqlite.org/lang_aggfunc.html
• Microsoft Learn, GROUP BY (Transact-SQL): a SELECT statement clause that divides the query result into groups of rows, usually by performing one or more aggregations on each group, returning one row for each group; ROLLUP, CUBE, and GROUPING SETS options; GROUP BY () calculating the grand total. https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql
• SERP check, literal phrase "group by en sql", checked 12 Nov 2026: top results were the Microsoft Learn GROUP BY (Transact-SQL) reference, a university SQL quick reference, a GROUP BY lesson with MySQL examples, and Spanish language GROUP BY guides. Common pattern: reference style documentation and short examples, with little sustained worked arithmetic on one reused dataset, little coverage of NULL grouping and COUNT distinctions, and little attention to bare column behavior across dialects. This article differentiates on a single reused dataset with every result executed against a real database and hand checked, stage by stage WHERE then HAVING reasoning, and explicit debugging steps for wrong totals and missing groups.