How GROUP BY Works in SQL: Organizing Data Into Categories

GROUP BY collapses rows that share the same value in one or more columns, and lets you run calculations across each group

When you write a GROUP BY clause in SQL, you are telling the database to bundle together all rows where a specific column has the same value, then treat each bundle as a single unit. This is useful when you want to count things, sum amounts, or find averages — not for individual rows, but for categories of rows.

For example, if you have a table of sales with columns for salesperson name, product sold, and amount, a GROUP BY on the salesperson name column will create one row of output for each unique salesperson. You can then calculate how much each person sold in total, how many transactions they made, or what their average sale was.

Key Takeaways

GROUP BY bundles rows that have the same value in a specified column and produces one output row per unique value.
You almost always use GROUP BY with an aggregate function like SUM(), COUNT(), AVG(), or MAX() to calculate something across each group.
If you GROUP BY multiple columns, the database creates separate groups for each unique combination of values in those columns.
Any column you select in your query must either be in the GROUP BY clause or wrapped in an aggregate function, or the query will fail.

The basic structure: SELECT, GROUP BY, and aggregate functions

A typical GROUP BY query has three parts. First, you SELECT the column you are grouping by, plus any aggregate functions you want to calculate. Second, you specify which table to read FROM. Third, you add the GROUP BY clause with the column name.

Here is a concrete example. Suppose you have a table called orders with columns customer_id, product, and amount. This query counts how many orders each customer placed:

SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;

The output will have one row per unique customer_id, with the count of their orders in the second column. The COUNT(*) function counts the rows in each group, and GROUP BY customer_id tells the database which rows belong together.

Common aggregate functions you use with GROUP BY

GROUP BY is almost always paired with a function that calculates something across the grouped rows. The most common ones are COUNT(), SUM(), AVG(), MIN(), and MAX().

COUNT(*) counts how many rows are in each group. COUNT(column_name) counts non-null values in that column within each group. SUM(amount) adds up all values in the amount column for each group. AVG(amount) calculates the average. MIN() and MAX() find the smallest and largest values in each group.

If you want to know the total sales amount per salesperson, you would write:

SELECT salesperson, SUM(amount) FROM sales GROUP BY salesperson;

This produces one row per salesperson with their total sales. If you wanted their average sale instead, you would replace SUM(amount) with AVG(amount).

Grouping by multiple columns

You can GROUP BY more than one column. When you do, the database creates a separate group for each unique combination of values in those columns.

For example, if you have a table of store transactions with columns store_location, product_category, and sale_amount, this query shows total sales by both location and category:

SELECT store_location, product_category, SUM(sale_amount) FROM transactions GROUP BY store_location, product_category;

The output will have one row for each unique pair of store_location and product_category. If you have 5 stores and 4 product categories, you could get up to 20 rows in the result (though fewer if some combinations do not exist in your data).

The rule: every non-aggregated column must be in GROUP BY

SQL enforces a strict rule: if you SELECT a column that is not wrapped in an aggregate function, that column must appear in the GROUP BY clause. If you break this rule, the database will return an error.

This query will fail:

SELECT customer_id, product, COUNT(*) FROM orders GROUP BY customer_id;

The product column is selected but not in the GROUP BY clause and not inside an aggregate function. The database does not know which product to show for each customer (since a customer might have ordered many different products). To fix it, either add product to the GROUP BY clause, or wrap it in an aggregate function like MAX(product) or MIN(product).

Filtering groups with HAVING instead of WHERE

The WHERE clause filters rows before they are grouped. The HAVING clause filters groups after they are created. This matters because you cannot use aggregate functions in a WHERE clause, but you can in a HAVING clause.

Suppose you want to find customers who placed more than 5 orders. You cannot write WHERE COUNT(*) > 5 because WHERE runs before grouping. Instead, you use HAVING:

SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING COUNT(*) > 5;

This groups the orders by customer, then filters out any groups where the count is 5 or fewer. The result shows only customers with more than 5 orders.

Real-world example: analyzing sales data

Imagine you work with a sales table that has columns for date, salesperson, region, and amount. You want to know how much each region sold in total, and only show regions that sold more than $50,000.

You would write:

SELECT region, SUM(amount) FROM sales GROUP BY region HAVING SUM(amount) > 50000;

This groups all sales by region, calculates the total for each region, and then filters to show only regions above $50,000. If you wanted to see the results sorted from highest to lowest, you could add ORDER BY SUM(amount) DESC at the end.

You could also group by both region and salesperson to see each salesperson's total within each region:

SELECT region, salesperson, SUM(amount) FROM sales GROUP BY region, salesperson;

Frequently Asked Questions

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping happens. HAVING filters the groups after they are created. You use WHERE to exclude rows from consideration, and HAVING to exclude entire groups from the output. You can use aggregate functions like COUNT() or SUM() in HAVING, but not in WHERE.

Can I GROUP BY a column I did not SELECT?

Yes. You can GROUP BY a column without selecting it. For example, you could GROUP BY customer_id but only SELECT the SUM(amount). The grouping still happens, but the customer_id values do not appear in the output. This is sometimes useful but usually confusing, so most people SELECT the grouped column too.

What happens if I GROUP BY a column with NULL values?

NULL values are treated as a single group. All rows with NULL in the grouped column will be bundled together into one group in the output. This can be surprising if you are not expecting it, so check your data if your results include an unexpected NULL row.

Does GROUP BY sort the results?

Not necessarily. Some databases sort GROUP BY results, but you should not rely on it. If you need a specific order, use the ORDER BY clause at the end of your query to sort the grouped results however you want.

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.