Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 125 ยท Aggregation

GROUP BY

Learn how SQL GROUP BY splits rows into groups so COUNT, SUM and AVG work per group. Group by several columns and expressions, and avoid the classic mistakes.

In Aggregate Functions you squeezed a whole table into a single row: one count, one total, one average. That answers questions like "how many products do we sell?" But real questions usually come with the word per: how many products per category, revenue per month, average grade per course.

GROUP BY is the clause that answers "per" questions. It splits the rows into groups that share a value, runs your aggregate functions once for each group, and returns one row per group. It's one of the most useful clauses in SQL, and once it clicks you'll use it in almost every report you write.

Every example runs against the shop sample database: categories, products, customers, orders and order_items.

From one total to one row per group

Start with a plain aggregate. This counts every product and averages every price:

SQL One row for the whole table ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

You get one row: 26 products with an average price of 53.69. Now add GROUP BY category_id and put the grouping column in the SELECT list:

SQL One row per category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Now there are six rows, one for each category. The aggregates no longer look at the whole table; each one only sees the rows of its own group. Category 2 (Outdoor), for example, has 5 products, and the most expensive one costs 189.

How GROUP BY works

A good mental model is a set of buckets:

  1. The database reads the rows that survived FROM and WHERE.
  2. It puts every row into a bucket labeled with its GROUP BY value. All products with category_id = 1 go into bucket 1, and so on.
  3. For each bucket it computes the aggregates (COUNT(*), AVG(price), โ€ฆ) over just the rows inside.
  4. It returns one row per bucket. The individual rows are gone; only the bucket label and the aggregate results remain.

That last step is the key to everything else in this lesson. After grouping, a "row" is a whole group, so the only things you can sensibly show are the group's label and summaries of its rows.

WHERE runs before the buckets are filled, so it decides which rows take part at all. This counts only products that are in stock:

SQL WHERE runs before grouping ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Three products have a stock of 0, so the counts for categories 1, 3 and 6 drop by one compared with the previous result. To filter on the results of the aggregates instead (for example "categories with more than four products") you need HAVING, which is the topic of the next lesson.

Sort your groups explicitly. SQLite often returns groups in label order because of how it builds them, but the SQL standard promises no order at all. Other databases may return groups in any order. Add ORDER BY whenever the order matters, and you can sort by an aggregate alias like in_stock.

The golden rule: grouped or aggregated

Every expression in the SELECT list of a grouped query must be one of these:

  • a column (or expression) listed in GROUP BY, or
  • an aggregate function such as COUNT, SUM, AVG, MIN or MAX, or
  • a constant.

Why? Think about the buckets. Category 1 contains five products with five different names. If you ask for name without aggregating it, which of the five names should the database print? There is no right answer.

Most databases refuse such a query. PostgreSQL, for example, stops with an error like this:

SQL
-- PostgreSQL
SELECT category_id, name, COUNT(*)
FROM products
GROUP BY category_id;
-- ERROR: column "products.name" must appear in the GROUP BY clause
--        or be used in an aggregate function

SQLite is unusually forgiving: it accepts the query and picks the value from some row of the group. Run it and see:

SQL A bare column (SQLite only) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

You get a name for each category, but nothing guarantees which one. Code like this tends to "work" during development and then break when the data or the database changes.

SQLite has one documented special case: if the query uses exactly one MIN() or MAX(), the bare columns come from the row that holds that minimum or maximum. So SELECT category_id, name, MAX(price) โ€ฆ GROUP BY category_id really does show the most expensive product of each category. It's handy, but it only works in SQLite. The portable way to get "the top row per group" uses window functions, which you'll meet in ROW_NUMBER, RANK and DENSE_RANK.

Database Non-grouped column in SELECT
SQLite Allowed; value from an arbitrary row (or the MIN/MAX row)
PostgreSQL Error, unless you group by the table's primary key
MySQL 5.7+ / 8 Error with the default ONLY_FULL_GROUP_BY mode
SQL Server Error
Oracle Error

PostgreSQL and MySQL do allow one shortcut that the standard permits: if you group by a table's primary key, you may select the table's other columns, because they can only have one value per group.

Write portable grouped queries. Even in SQLite, follow the golden rule: either group by a column or aggregate it. If you need a value that is the same for the whole group, add it to GROUP BY too; it doesn't change the groups.

Grouping by several columns

List more than one column and you get one group for each combination of values. Customers live in cities inside countries, so grouping by both counts customers per city:

SQL Customers per country and city ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Every country in this shop has customers in only one city, so you get 13 rows, one per country. Change it to GROUP BY country alone (and remove city from the SELECT list, following the golden rule) and the result has the same 13 groups. With richer data, adding a column splits each group into smaller ones.

The order of the columns in GROUP BY doesn't change which groups exist. GROUP BY country, city and GROUP BY city, country produce the same groups; only ORDER BY decides how they're sorted.

A practical two-column example uses the orders table. How many orders are there per customer and status?

SQL Orders per customer and status ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Customer 21 has two cancelled orders and one delivered order, so it appears on two rows: one for each status it actually has. Combinations that don't occur in the data don't appear at all. Showing every status as its own column, with zeros for missing ones, is called pivoting, and you'll learn it in Pivoting Rows into Columns.

Grouping by an expression

You can group by any expression, not just a stored column. The most common example is grouping timestamps by month. ordered_at holds text like '2025-03-17 12:45', and strftime('%Y-%m', ordered_at) turns it into '2025-03':

SQL Orders per month ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

There are twelve rows, one per month of 2025. March and May were the busiest months with 16 orders each; April had only 3. You'll learn more ways to slice dates in Date and Time Functions.

A CASE expression makes your own buckets. Here products are sorted into price bands:

SQL Products per price band ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The numbers at the start of each label make the bands sort in a sensible order. This query also groups by the alias price_band instead of repeating the whole CASE. SQLite, PostgreSQL and MySQL allow that; SQL Server and Oracle don't, because in standard SQL the SELECT list is evaluated after GROUP BY. If you need portable code, repeat the expression in GROUP BY, as the monthly query did.

Some databases also let you group by position: GROUP BY 1 means "the first column of the SELECT list". It's quick to type in an ad hoc query, but it breaks silently when someone reorders the columns, so avoid it in saved code.

NULL forms its own group

For grouping purposes, all NULL values are treated as equal, so they land in one shared group. The school database has students without a homeroom:

SQL NULL is a group too ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

You get four groups: NULL with 4 students, then A, B and C. This is different from WHERE homeroom = NULL, which never matches anything (see Working with NULL). If you prefer a readable label, group by COALESCE(homeroom, 'none') instead.

GROUP BY and DISTINCT

A grouped query with no aggregates returns each distinct combination once, exactly like DISTINCT:

SQL
SELECT DISTINCT country FROM customers;

SELECT country FROM customers GROUP BY country;

Both return the 13 countries. Use DISTINCT when you only want the unique values, and GROUP BY when you also want to compute something per value. You can mix the two ideas inside an aggregate: COUNT(DISTINCT city) counts the different cities in each group.

Empty input: a subtle difference

What happens when no rows match? Without GROUP BY, an aggregate query always returns exactly one row, because the whole (empty) table is one group. With GROUP BY, there are no rows to put into buckets, so you get no rows at all:

SQL No rows in, how many rows out? ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query returns one row with 0; the second returns an empty result. Keep this in mind when application code expects "a count of zero" from a grouped query: missing groups simply aren't there.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. The products table has 26 rows spread over 6 categories. How many rows does SELECT category_id, COUNT(*) FROM products GROUP BY category_id; return?

    GROUP BY returns one row per group, and there are six distinct category_id values.
  2. 2. Which query follows the golden rule of GROUP BY and runs in every major database?

    Every selected expression is either the grouping column or an aggregate. The other two select a column that is neither grouped nor aggregated.
  3. 3. What does GROUP BY strftime('%Y-%m', ordered_at) do?

    You can group by any expression. Every order with the same year-month text lands in the same group.
  4. 4. A column contains the values 'A', NULL, 'B', NULL. How many groups does GROUP BY on that column create?

    For grouping, NULLs are treated as equal and form a single group, unlike in comparisons with =.
  5. 5. A WHERE clause matches no rows. What does a query with GROUP BY and COUNT(*) return?

    With GROUP BY there are no groups to report, so the result is empty. Without GROUP BY you would get one row with 0.

Summary

  • GROUP BY puts rows into groups that share a value and returns one row per group.
  • Aggregates in a grouped query are computed separately for each group.
  • Every selected expression must be grouped, aggregated or constant. SQLite tolerates bare columns, but other databases reject them, and the value is arbitrary.
  • Group by several columns to get one group per combination, and by expressions (dates, CASE) to make your own buckets.
  • WHERE filters rows before grouping; NULL values form one group; grouped queries over no rows return no rows.
  • Add ORDER BY whenever the order of the groups matters.

Next: HAVING, where you'll filter the groups themselves.