What GROUP BY Does in SQL — Organizing Data Into Rows That Match

GROUP BY collapses rows that share the same value into a single row, then lets you count, sum, or average them

When you write a GROUP BY clause in SQL, you are telling the database: "Look at this column. Wherever the value repeats, treat all those rows as one group. Then show me one row per group." The database collapses duplicates and gives you a summary instead of a list.

Think of a spreadsheet where you have 500 rows of sales data — each row is one transaction, with a date, a product name, and a dollar amount. If you want to know how much was sold per product, not per transaction, GROUP BY does that work. You say "group by product name" and the database returns one row per product, with the total sales for each one.

GROUP BY almost always works with an aggregate function — a function that does math across multiple rows. The most common ones are COUNT (how many rows), SUM (add them up), AVG (average), MIN (smallest), and MAX (largest). Without an aggregate function, GROUP BY has nothing to calculate, so it is rarely useful on its own.

Key Takeaways

GROUP BY takes a column name and collapses all rows with the same value in that column into a single row.
You almost always pair GROUP BY with an aggregate function like COUNT, SUM, or AVG to calculate something across the grouped rows.
The result shows one row per unique value in the grouped column, plus whatever calculation you asked for.
You can group by more than one column, which creates groups only when both columns match.
HAVING is the WHERE clause for groups — it filters the results after grouping happens, not before.

How GROUP BY actually works in a query

A basic GROUP BY query looks like this:

SELECT product_name, SUM(sale_amount) FROM sales GROUP BY product_name;

The database reads this as: "Show me the product name and the total of all sale amounts, grouped by product name." It scans the entire sales table, finds every unique product name, adds up all the sale amounts for each one, and returns one row per product.

The SELECT clause can only contain two things: the column you grouped by (product_name) and an aggregate function (SUM). You cannot ask for a column that was not grouped by, because the database would not know which row's value to show — if you grouped 50 transactions into one row, which transaction's date should it display?

If you want to count how many transactions happened per product instead of the total amount, you change the aggregate function:

SELECT product_name, COUNT(*) FROM sales GROUP BY product_name;

Now the result shows each product name and how many rows had that product name.

Grouping by more than one column

You can group by two or more columns at once. The database then creates a group only when both (or all) columns match.

SELECT product_name, region, SUM(sale_amount) FROM sales GROUP BY product_name, region;

This returns one row per combination of product and region. If you sold Widget A in the North and Widget A in the South, those are two separate groups with two separate rows in the result. The database only collapses rows when both the product name and the region are identical.

Grouping by multiple columns is useful when you want a breakdown by more than one dimension — sales per product per region, or customer count per state per age group, for example.

HAVING — filtering groups after they are created

WHERE filters rows before grouping happens. HAVING filters groups after grouping happens. This matters because you cannot use an aggregate function in a WHERE clause.

If you want to show only products with more than 100 sales, you cannot write:

SELECT product_name, COUNT(*) FROM sales WHERE COUNT(*) > 100 GROUP BY product_name;

That fails because WHERE runs before grouping, so COUNT(*) does not exist yet. Instead, use HAVING:

SELECT product_name, COUNT(*) FROM sales GROUP BY product_name HAVING COUNT(*) > 100;

Now the database groups first, calculates the count for each group, and then filters to show only groups where the count is greater than 100. HAVING always comes after GROUP BY.

Common mistakes with GROUP BY

The most frequent error is trying to SELECT a column that was not grouped by and has no aggregate function. If you write:

SELECT product_name, sale_date, SUM(sale_amount) FROM sales GROUP BY product_name;

The database does not know which sale_date to show for each product, because there may be dozens of dates. Most databases reject this query outright. Some older versions allow it but return unpredictable results.

Another common mistake is forgetting that GROUP BY removes all duplicate rows. If you group by product name and then ask for the sale date, you are asking the database to pick one date from potentially many — it will not do that without you telling it how (with MIN, MAX, or another aggregate function).

A third mistake is using WHERE when you meant HAVING. WHERE filters before grouping, so if you write WHERE COUNT(*) > 100, it fails. HAVING filters after grouping, so it works with aggregate functions.

When you actually use GROUP BY

GROUP BY is for any question that starts with "how many" or "how much" broken down by category. How many customers per state? How much revenue per product per month? What is the average order size per customer? All of these need GROUP BY.

It is also how you find duplicates. If you want to know which email addresses appear more than once in a customer table, you group by email address, count the rows, and filter for counts greater than one:

SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1;

GROUP BY is one of the most useful parts of SQL because most real questions about data are not "show me every row" but "show me a summary broken down by category."

Frequently Asked Questions

Can I use GROUP BY without an aggregate function?

Technically yes, but it is almost never useful. GROUP BY without an aggregate function just removes duplicates — it shows one row per unique value in the grouped column. If you want to remove duplicates, use DISTINCT instead, which is clearer and simpler.

What happens if I group by a column with NULL values?

NULL values are treated as a group of their own. All rows with NULL in the grouped column collapse into a single group. If you have 50 rows with NULL in the product_name column, they become one row in the result with whatever aggregate you asked for.

Can I order the results of a GROUP BY query?

Yes, with ORDER BY. You can sort by the grouped column, by the aggregate result, or by both. For example, ORDER BY SUM(sale_amount) DESC shows the products with the highest total sales first.

What is the difference between GROUP BY and DISTINCT?

DISTINCT removes duplicate rows and shows each unique combination once. GROUP BY collapses rows and lets you calculate something across them. If you just want unique values with no math, use DISTINCT. If you want to count, sum, or average, use GROUP BY with an aggregate function.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.