Sign in Get started

SQL Tutorial · Aggregation and Functions · Lesson 14 of 36

SQL GROUP BY

Group rows and aggregate each group.

GROUP BY

GROUP BY splits rows into groups and computes aggregates per group:

Example 1Try it yourself
SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
ORDER BY customers DESC, country;

Output

country | customers
USA | 5
UK | 2
Japan | 1
Netherlands | 1
Switzerland | 1

Every column in the SELECT list must either be in GROUP BY or inside an aggregate function. (SQLite and old MySQL versions are lenient about this; PostgreSQL is strict — write it correctly anyway.)

Grouping by several columns

Example 2Try it yourself
SELECT department, title, COUNT(*) AS people, AVG(salary) AS avg_salary
FROM employees
GROUP BY department, title
ORDER BY department, avg_salary DESC;

Output

department | title | people | avg_salary
Engineering | CTO | 1 | 210000
Engineering | Engineering Manager | 1 | 165000
Engineering | Senior Engineer | 1 | 140000
Engineering | Engineer | 2 | 113500
Management | CEO | 1 | 250000
Sales | Head of Sales | 1 | 150000
Sales | Account Executive | 2 | 89000
Support | Support Specialist | 1 | 65000

Test yourself

Which clause computes aggregates per country?

  1. ORDER BY country
  2. GROUP BY country
  3. PARTITION country
  4. WHERE country
Show answer

Answer: GROUP BY country