Lesson 12 of 26 · see the practice database

Grouping rows with GROUP BY

The last lesson produced one figure for a whole table. GROUP BY produces one figure for each group: the number of products in each category, the number of customers in each city, the total salary of each department. It is the heart of every report.

The idea

GROUP BY sorts the rows into piles, one pile for each different value of a column. Then the aggregate functions run once for each pile, and each pile becomes one row of the result.

SELECT category, COUNT(*) AS products
FROM products
GROUP BY category;

There are four categories, so there are four rows. Each shows the category and how many products are in its pile.

Any aggregate works

SELECT category,
       COUNT(*) AS products,
       MIN(price) AS cheapest,
       MAX(price) AS dearest,
       ROUND(AVG(price)) AS average
FROM products
GROUP BY category;

SELECT department, SUM(salary) AS monthly_cost
FROM employees
GROUP BY department;

The one big rule

Every column in the SELECT list must be either:

1. named in the GROUP BY, or
2. inside an aggregate function.

The reason is the same as in the last lesson. Each group becomes one row. A column that is the same for the whole group, because you grouped by it, has one value to show. A column that varies inside the group does not, unless you say how to boil it down.

SELECT category, name, COUNT(*)
FROM products
GROUP BY category;

Which of the four Grocery product names should appear on the one Grocery row? PostgreSQL, SQL Server and Oracle stop with an error. SQLite picks a name from some row of the group without complaint, which looks like an answer and is not one. Follow the rule and your queries will work everywhere and mean what they say.

Sorting the groups

ORDER BY comes after GROUP BY. You can sort by the aggregate, usually through its alias.

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
ORDER BY customers DESC, city;

Add LIMIT for a top list. Which product has been ordered in the largest quantity?

SELECT product_id, SUM(quantity) AS units
FROM order_items
GROUP BY product_id
ORDER BY units DESC
LIMIT 3;

The result shows product numbers, not names, because the names live in another table. Bringing them in is what joins are for.

Filtering before grouping

WHERE runs first. It decides which rows go into the piles at all.

SELECT status, COUNT(*) AS orders
FROM orders
WHERE order_date >= '2026-04-01'
GROUP BY status;

Only the orders from April onwards are counted. Orders before that never reach a pile.

Grouping by two columns

With two columns in the GROUP BY, there is one pile for each combination that exists.

SELECT state, city, COUNT(*) AS customers
FROM customers
GROUP BY state, city
ORDER BY state, city;

Grouping by a calculation

You can group by something worked out. This counts orders in each month by cutting the year and month from the date.

SELECT SUBSTR(order_date, 1, 7) AS month, COUNT(*) AS orders
FROM orders
GROUP BY SUBSTR(order_date, 1, 7)
ORDER BY month;

Repeating the calculation in the GROUP BY is the form that works in every database. SQLite, MySQL and PostgreSQL also let you write GROUP BY month, using the alias. SQL Server and older versions of Oracle do not.

Here is another: how many customers joined in each year?

SELECT SUBSTR(joined, 1, 4) AS year, COUNT(*) AS new_customers
FROM customers
GROUP BY SUBSTR(joined, 1, 4);

How NULL is grouped

All the rows with NULL in the grouping column form one group of their own.

SELECT manager_id, COUNT(*) AS staff
FROM employees
GROUP BY manager_id;

The row with no manager number is the person at the top.

Groups with nothing in them do not appear

GROUP BY makes piles only from rows that exist. Three of our customers have never ordered, so they are simply absent here:

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

Nine customers appear, not twelve. A report that should show a zero for the other three needs a LEFT JOIN. Remember this whenever a summary seems to have lost a few rows.

GROUP BY without an aggregate

This works, and returns each value once, exactly like DISTINCT.

SELECT department FROM employees GROUP BY department;

The order in which a query runs

The order in which you write the parts is not the order in which the database does them. Knowing the real order explains most of the rules you have met.

1. FROM: take the table.
2. WHERE: throw away rows that fail the test.
3. GROUP BY: sort the remaining rows into piles.
4. Aggregates: work out a value for each pile.
5. HAVING: throw away piles that fail a test (next lesson).
6. SELECT: choose and name the columns.
7. ORDER BY: sort the result.
8. LIMIT: cut it down.

That is why WHERE cannot use an aggregate or an alias, and ORDER BY can use both.

Try it yourself

SELECT department, COUNT(*) FROM employees GROUP BY department;

SELECT category, SUM(stock) FROM products GROUP BY category;

SELECT status, COUNT(*) AS orders FROM orders GROUP BY status ORDER BY orders DESC;

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.