GROUP BY

Split data into groups and aggregate each one separately

Intermediate 20 minGROUP BYaggregationHAVING

What Does GROUP BY Do in SQL?

GROUP BY collects rows that have the same value in one or more columns into groups and returns one row per group. Aggregate functions such as COUNT, SUM and AVG then compute one value for each group instead of one value for the whole table.

One row per group: the 10 products fall into 4 categories, so GROUP BY category with COUNT(*) returns 4 rows: Electronics 4, Furniture 4, Appliances 1 and Stationery 1. Without GROUP BY, COUNT(*) returns a single row for the whole table: 10.

Several columns: GROUP BY col1, col2 makes one group for each combination of values that actually occurs in the data.

The rule: every column in SELECT must either appear in GROUP BY or sit inside an aggregate function. A group holds many rows, so a plain column could have many different values and there is no single one to show. SQLite lets you break this rule; PostgreSQL, MySQL and SQL Server don't (details below).

Filtering: WHERE removes rows before they are grouped, and HAVING removes whole groups after the aggregates are computed.

Knowledge Canvas

How GROUP BY Works

Collapse rows into groups

GROUP BY collects rows with matching values into a single group
Each group produces exactly one output row
Non-aggregated columns in SELECT MUST be in GROUP BY
Multiple columns = one group per combination that occurs

Mental Model

Think of it as sorting into buckets

GROUP BY country → one box per country. GROUP BY country, city → one box per country+city combination.

Imagine physically sorting rows into labeled boxes
Each box = one unique combination of GROUP BY columns
Aggregates (COUNT, SUM, AVG) summarize what's inside each box
The result has one row per box

Logical Order

Where GROUP BY fits in the pipeline

FROM — pick the table(s)

WHERE — filter individual rows

GROUP BY — collapse rows into groups

HAVING — filter groups

SELECT — compute output columns

ORDER BY — sort the result

LIMIT — cap row count

GROUP BY Gotchas

The most common aggregation errors

Non-aggregated column not in GROUP BY → error in PostgreSQL, MySQL and SQL Server; SQLite returns a value from an arbitrary row
WHERE can't filter groups — use HAVING
SQL Server rejects SELECT aliases in GROUP BY
GROUP BY doesn't sort the output — add ORDER BY

GROUP BY Rules

Memorize these

Every non-aggregate in SELECT must appear in GROUP BY
GROUP BY runs after WHERE, before HAVING
All NULLs in a grouped column form one group
Column order in GROUP BY doesn't change the groups; only ORDER BY guarantees output order

Syntax

Syntax Template

1SELECT

2 group_col,

3 AGG(col) AS alias

4FROM table

5WHERE row_condition

6GROUP BY

7 group_col

8HAVING group_condition

9ORDER BY

10 alias DESC;

GROUP BY colOne output row per distinct value of col

GROUP BY col1, col2One row per combination that occurs; the order you list the columns in doesn't change the groups

AGG(col)COUNT, SUM, AVG, MIN or MAX, computed once for each group

WHEREOptional. Filters rows before they are grouped

HAVINGOptional. Filters groups after the aggregates are computed

ORDER BYThe only way to guarantee the order of the groups

RuleEvery non-aggregated SELECT column must be listed in GROUP BY

Sample Data

orders

12 rows

id	customer_id	order_date	status	total
5001	C1001	2024-01-15	delivered	59.98
5002	C1002	2024-02-08	delivered	449
5003	C1001	2024-02-20	shipped	12.5
5004	C1003	2024-03-05	delivered	89.99
5005	C1006	2024-03-15	pending	245
5006	C1005	2024-03-22	delivered	159
5007	C1004	2024-04-02	shipped	45.5
5008	C1009	2024-04-12	cancelled	79
5009	C1012	2024-04-22	delivered	425
5010	C1007	2024-05-05	pending	18.99
5011	C1008	2024-05-18	delivered	134
5012	C1010	2024-06-08	cancelled	65.5

products

10 rows

name	category	price
Wireless Mouse	Electronics	24.99
Office Desk	Furniture	189
LED Desk Lamp	Furniture	45.5
USB-C Cable	Electronics	12.99
Bluetooth Speaker	Electronics	79
Office Chair	Furniture	95
Coffee Maker	Appliances	89.99
Notebook Set	Stationery	8.5
Wireless Headphones	Electronics	159
Standing Desk	Furniture	425

customers

12 rows

id	name	country
C1001	Aarav Sharma	India
C1002	Sara Chen	Singapore
C1003	James Wilson	UK
C1004	Maria Garcia	Spain
C1005	Yuki Tanaka	Japan
C1006	Priya Patel	India
C1007	Alex Johnson	USA
C1008	Chen Wei	China
C1009	Emma Brown	Australia
C1010	Omar Hassan	UAE
C1011	Lena Muller	Germany
C1012	Ravi Kumar	India

GROUP BY with COUNT and AVG

Find the number of products and the average price in each category.

SQL

1SELECT

2 category,

3 COUNT(*) AS product_count,

4 ROUND(AVG(price), 2) AS avg_price

5FROM products

6GROUP BY

7 category

8ORDER BY

9 avg_price DESC;

GROUP BY category splits the 10 products into 4 groups, and COUNT and AVG then run once inside each group. Furniture and Electronics have 4 products each, averaging 188.63 and 69. Appliances and Stationery have 1 product each, so their average is simply that product's price: 89.99 and 8.5. ROUND keeps two decimals (the raw Furniture average is 188.625), and ORDER BY avg_price DESC lists the most expensive category first.

Output

4 rows

category	product_count	avg_price
Furniture	4	188.63
Appliances	1	89.99
Electronics	4	69
Stationery	1	8.5

GROUP BY with WHERE and HAVING

Among products priced over 50, find the categories that still have more than one product, with their product count and average price.

SQL

1SELECT

2 category,

3 COUNT(*) AS product_count,

4 ROUND(AVG(price), 2) AS avg_price

5FROM products

6WHERE price > 50

7GROUP BY

8 category

9HAVING COUNT(*) > 1

10ORDER BY

11 product_count DESC;

The clauses run in stages. WHERE keeps the 6 products priced over 50 and drops the other 4, which removes Stationery completely (its only product costs 8.5). GROUP BY puts the 6 remaining products into 3 groups: Furniture 3, Electronics 2 and Appliances 1. HAVING then drops Appliances, because COUNT(*) > 1 is false for it. WHERE also changes the averages: Furniture averages 236.33 here, against 188.63 for all 4 of its products, because the LED Desk Lamp (45.5) was filtered out before AVG ran.

Output

2 rows

category	product_count	avg_price
Furniture	3	236.33
Electronics	2	119

GROUP BY Multiple Columns

Count the orders in each month, split by status.

SQL

1SELECT

2 strftime('%Y-%m', order_date) AS order_month,

3 status,

4 COUNT(*) AS order_count

5FROM orders

6GROUP BY

7 order_month,

8 status

9ORDER BY

10 order_month,

11 status;

With two columns in GROUP BY, each group is one combination of month and status. The orders span 6 months and 4 statuses, so 24 combinations are possible, but only the 11 that actually occur come back. 2024-03 with delivered is the only group holding 2 orders (5004 and 5006); every other combination has 1. Writing GROUP BY status, order_month instead gives exactly the same 11 groups: the order of the columns in GROUP BY doesn't change the groups, and only ORDER BY guarantees the order of the rows. strftime('%Y-%m', order_date) turns 2024-03-05 into 2024-03. It is SQLite's date function; PostgreSQL uses to_char and MySQL DATE_FORMAT.

Output

11 rows

order_month	status	order_count
2024-01	delivered	1
2024-02	delivered	1
2024-02	shipped	1
2024-03	delivered	2
2024-03	pending	1
2024-04	cancelled	1
2024-04	delivered	1
2024-04	shipped	1
2024-05	delivered	1
2024-05	pending	1
2024-06	cancelled	1

GROUP BY with a JOIN

Show the number of orders and the total order value for each customer country.

SQL

1SELECT

2 c.country,

3 COUNT(*) AS order_count,

4 ROUND(SUM(o.total), 2) AS order_value

5FROM orders o

6INNER JOIN customers c

7 ON o.customer_id = c.id

8GROUP BY

9 c.country

10ORDER BY

11 order_value DESC;

Joins are covered later, in the Joins track. Here, country is stored in customers and totals in orders, so the INNER JOIN first attaches each order to its customer's country, and GROUP BY then groups the 12 joined rows by country. India leads with 4 orders worth 742.48, placed by 3 different customers (Aarav Sharma placed 2 of them); every other country has 1 order. Only 9 countries appear, although customers covers 10: Lena Muller in Germany has no orders, so the inner join drops her before any grouping happens. To list Germany with 0, start from customers with a LEFT JOIN and use COUNT(o.id), because COUNT(*) would count her unmatched row as 1 (see COUNT). The column is called order_value rather than revenue because it includes cancelled and pending orders.

Output

9 rows

country	order_count	order_value
India	4	742.48
Singapore	1	449
Japan	1	159
China	1	134
UK	1	89.99
Australia	1	79
UAE	1	65.5
Spain	1	45.5
USA	1	18.99

Logical Query Order and Column Aliases

You write SELECT first, but the database evaluates a grouped query in this logical order: FROM and JOIN → WHERE → GROUP BY → HAVING → SELECT (where aliases are created) → ORDER BY → LIMIT.

This order decides what each clause can see. The engine may physically run the steps in a different order, as SQL Server's documentation points out. Two consequences hold in every database:

WHERE can't use aggregates: no group exists yet when it runs (the third mistake below shows the error).

ORDER BY can use aliases: it runs after SELECT, so ORDER BY avg_price DESC works everywhere.

Whether the clauses that run before SELECT can use its aliases depends on the database:

In GROUP BY (GROUP BY order_month): SQLite, PostgreSQL and MySQL accept it; SQL Server doesn't. If the alias has the same name as a table column (strftime('%Y-%m', order_date) AS order_date ... GROUP BY order_date), all three group by the original column instead, so here you get 12 groups of 1 order rather than 6 months. Give the alias a new name such as order_month.

In HAVING (HAVING product_count > 1): SQLite and MySQL accept it; PostgreSQL and SQL Server don't.

In WHERE: only SQLite accepts it.

Portable habit: repeat the expression instead, as in HAVING COUNT(*) > 1. SQLite, PostgreSQL and MySQL (where it is deprecated) also accept GROUP BY 1 (the first SELECT column); SQL Server doesn't. It quietly changes meaning if someone reorders the SELECT list, so name the column instead.

GROUP BY vs ORDER BY, DISTINCT and Window Functions

GROUP BY doesn't sort. ORDER BY only sorts and keeps every row; GROUP BY merges rows into one per group and doesn't promise any order. SQLite usually returns groups sorted by the GROUP BY columns as a side effect: run the multiple-columns example without its ORDER BY, and GROUP BY order_month, status comes back month by month while GROUP BY status, order_month comes back status by status. PostgreSQL often groups with a hash table and returns groups in no particular order, and MySQL 8.0 stopped sorting GROUP BY results. When order matters, add ORDER BY.

GROUP BY without an aggregate works like DISTINCT. SELECT country FROM customers GROUP BY country returns the same 10 countries as SELECT DISTINCT country FROM customers (see DISTINCT). Use DISTINCT when you only want the unique values, and GROUP BY when you want a number for each of them.

GROUP BY always collapses rows. To keep every order and show its customer's total beside it, use a window function such as SUM(total) OVER (PARTITION BY customer_id) instead.

How GROUP BY Handles NULLs and Empty Results

NULLs form one group. GROUP BY treats every NULL in a column as the same value. 3 of the 12 reviews have no comment, so GROUP BY comment returns a single NULL row with a count of 3. NULLs in Aggregations shows how NULLs also change COUNT, SUM and AVG.

No rows, no groups. SELECT COUNT(*) FROM orders WHERE status = 'returned' returns one row containing 0, because an aggregate query without GROUP BY (and without HAVING) always returns exactly one row. Add GROUP BY status and the result is empty: no rows matched, so there are no groups.

Common Mistakes

❌

Selecting a column that isn't grouped or aggregated

SQL

1SELECT

2 category,

3 name,

4 COUNT(*) AS product_count

5FROM products

6GROUP BY

7 category;

Electronics has 4 products but becomes a single row, so there is no one name to show. SQLite runs the query anyway and fills name from an arbitrary row of each group, here Wireless Mouse for Electronics and Office Desk for Furniture. Nothing makes those the right products, and PostgreSQL, MySQL and SQL Server reject the query (their exact errors are below).

Remove name, or aggregate it so the value is defined: MIN(name) gives the first name alphabetically, and STRING_AGG(name, ', ') lists all of them. That spelling works here (SQLite 3.44+), in PostgreSQL and in SQL Server; MySQL uses GROUP_CONCAT(name). Adding name to GROUP BY also runs, but then every product becomes its own group.

❌

Using a bare column to find the top row per group

SQL

1SELECT

2 category,

3 name,

4 MAX(price) AS top_price

5FROM products

6GROUP BY

7 category;

This looks like it finds the most expensive product in each category, and in SQLite it does: Standing Desk (425), Wireless Headphones (159), Coffee Maker (89.99) and Notebook Set (8.5). That relies on a SQLite-only rule: when the query has exactly one MIN() or MAX(), bare columns come from the row that holds that extreme value. With ties the row is arbitrary, and PostgreSQL, MySQL and SQL Server reject the query.

Find the maximum first, then join back for the full row: compute MAX(price) per category in a CTE and join it to products on both category and price. Numbering the rows in each category with ROW_NUMBER() and keeping row 1 also works in every database; that lesson solves exactly this query.

❌

Putting an aggregate in WHERE

SQL

1SELECT

2 category,

3 COUNT(*) AS product_count

4FROM products

5WHERE COUNT(*) > 1

6GROUP BY

7 category;

SQLite stops with misuse of aggregate: COUNT(), and PostgreSQL with aggregate functions are not allowed in WHERE. WHERE runs before any group exists, so there is no count to compare yet.

Put conditions on aggregates in HAVING, after GROUP BY: HAVING COUNT(*) > 1 returns Electronics (4) and Furniture (4). Row conditions such as price > 50 stay in WHERE.

❌

Grouping by a unique column like id

SQL

1SELECT

2 id,

3 customer_id,

4 SUM(total) AS order_value

5FROM orders

6GROUP BY

7 id,

8 customer_id;

Adding every selected column to GROUP BY silences the ungrouped-column error that PostgreSQL, MySQL and SQL Server raise (quoted in the next section), but id is different on every order, so every group holds exactly one row. The query returns all 12 orders, each with its own total: the same as no GROUP BY at all.

Group only by the level you report on and drop id: GROUP BY customer_id returns 11 rows, one per customer who has ordered. C1001 is the only customer with 2 orders, worth 72.48 together (wrap the sum in ROUND, since the raw value is 72.47999999999999).

GROUP BY Errors in PostgreSQL, MySQL and SQL Server

SQLite, the engine behind this site, Python's sqlite3 module and DB Browser for SQLite, accepts "bare" columns: columns in a grouped query that are neither listed in GROUP BY nor inside an aggregate. It fills each one from some row of the group without saying which (SQLite lists this among its quirks), which is why the first two mistakes above run here without an error. Other databases refuse to guess. For SELECT category, name, COUNT(*) FROM products GROUP BY category they report:

PostgreSQL: column "products.name" must appear in the GROUP BY clause or be used in an aggregate function

MySQL (error 1055):

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.products.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

. ONLY_FULL_GROUP_BY has been on by default since MySQL 5.7.5; with it turned off, MySQL picks a value the way SQLite does.

SQL Server (error 8120):

Column 'products.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Oracle: error ORA-00979, for the same rule.

The fix is the same in every database: add the column to GROUP BY, or wrap it in an aggregate that says which value you want. Two details differ:

Primary keys: PostgreSQL and MySQL accept an ungrouped column when GROUP BY includes its table's primary key, because the key determines the rest of the row: GROUP BY c.id lets you select c.name from customers. SQL Server needs GROUP BY c.id, c.name.

Any value on purpose: if you truly don't care which value appears, MySQL and PostgreSQL 16+ let you write ANY_VALUE(name). SQLite 3.49, which this site runs, doesn't have it.

Key Concepts

1GROUP BY returns one row per distinct value, or per combination of values, in the grouped columns

2Every SELECT column must be grouped or aggregated; SQLite tolerates a bare column, but PostgreSQL, MySQL and SQL Server reject it

3WHERE filters rows before grouping; HAVING filters groups after

4GROUP BY does not sort the output: add ORDER BY

5NULLs in a grouped column form a single group

6Logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

Pro Tip

Before writing the query, finish the sentence "one row per ___". Whatever fills the blank goes in GROUP BY, and every other SELECT column needs an aggregate. For "one row per customer", that means GROUP BY customer_id, not GROUP BY customer_id, id.

When to Use

Revenue by product category, orders per customer, average rating per product, monthly sales totals, users by country.

Challenge

Solve the problem below

Excluding cancelled orders, show each month's number of orders and total order value from the orders table. Name the columns order_month (in YYYY-MM form), order_count and order_value, and sort by order_month.

Your Query

MIN & MAX

HAVING