SQL GROUP BY Explained Simply (With Examples)

So far every query has returned one result row for each table row. But many real questions are about totals: How many employees are in each department? What is the average salary per city?

GROUP BY answers exactly these questions. It collects rows that share a value into groups, then calculates one summary row per group.

The example table#

First, aggregate functions#

GROUP BY works together with aggregate functions. An aggregate function takes many values and returns a single value:

Without GROUP BY, an aggregate treats the whole table as one big group:

SELECT COUNT(*) AS employees, AVG(salary) AS avg_salary
FROM employees;

employees | avg_salary
----------+-----------
5         | 63600

GROUP BY syntax#

SELECT group_column, AGGREGATE(other_column)
FROM table_name
GROUP BY group_column;

Count rows per group#

How many employees work in each department?

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

department  | employees
------------+----------
Engineering | 2
Marketing   | 1
Sales       | 2

Here is what the database does:

1. It sorts the five rows into piles, one pile per department: Sales (Asha, Priya), Engineering (Ravi, Meera), Marketing (John).
2. For each pile it runs COUNT(*).
3. It returns one row per pile.

Several aggregates at once#

You can calculate as many summaries as you like for each group:

SELECT department,
       COUNT(*)    AS employees,
       SUM(salary) AS total_salary,
       AVG(salary) AS avg_salary,
       MAX(salary) AS highest_salary
FROM employees
GROUP BY department
ORDER BY total_salary DESC;

department  | employees | total_salary | avg_salary | highest_salary
------------+-----------+--------------+------------+---------------
Engineering | 2         | 159000       | 79500      | 81000
Sales       | 2         | 113000       | 56500      | 61000
Marketing   | 1         | 46000        | 46000      | 46000

Group by more than one column#

When you group by two columns, each unique combination becomes a group:

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

department  | city      | employees
------------+-----------+----------
Engineering | Bengaluru | 1
Engineering | Pune      | 1
Marketing   | Mumbai    | 1
Sales       | Delhi     | 1
Sales       | Mumbai    | 1

Filter rows first with WHERE#

WHERE runs before grouping, so it decides which rows go into the piles at all:

SELECT department, COUNT(*) AS high_earners
FROM employees
WHERE salary > 55000
GROUP BY department;

department  | high_earners
------------+-------------
Engineering | 2
Sales       | 1

Asha (52,000) and John (46,000) were removed before grouping, so Marketing disappears completely and Sales only counts Priya.

Filter groups with HAVING#

What if you want to filter on the result of an aggregate, such as "departments with more than one employee"? WHERE cannot do this, because when WHERE runs, the groups do not exist yet. Use HAVING, which runs after grouping:

SELECT department, COUNT(*) AS employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

department  | employees
------------+----------
Engineering | 2
Sales       | 2

The order SQL runs a grouped query#

SELECT department, AVG(salary) AS avg_salary  -- 5. pick columns
FROM employees                                -- 1. take the table
WHERE city <> 'Delhi'                         -- 2. remove rows
GROUP BY department                           -- 3. make groups
HAVING AVG(salary) > 50000                    -- 4. remove groups
ORDER BY avg_salary DESC;                     -- 6. sort the result

Remembering this order explains most GROUP BY errors. For example, it is why you can use the alias avg_salary in ORDER BY (step 6 comes after SELECT) but most databases will not let you use it in HAVING (step 4 comes before).

Common mistakes#

Practice#

SELECT city,
       COUNT(*)    AS employees,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY city
HAVING COUNT(*) >= 2;

city   | employees | avg_salary
-------+-----------+-----------
Mumbai | 2         | 49000

Key takeaway#

Next, learn how to combine data from two tables with INNER JOIN.