SQL GROUP BY: compute per group

Aggregate functions summarize a whole table into a single value. But you often want a result per category: the number of customers per city, the revenue per month, the average salary per department. That is the job of GROUP BY.

Syntax

SELECT group_column, FUNCTION(column)
FROM table
GROUP BY group_column;

How it works

GROUP BY gathers the rows that have the same value in the given column, then applies the aggregate function to each group. The result contains one row per group.

The golden rule

Every column in the SELECT must either appear in the GROUP BY or be inside an aggregate function. Otherwise, SQL would not know which value to show for a group that contains several different rows.

The execution order of a query

SQL does not run the clauses in the order you write them:

1. FROM (and the JOINs): start from the tables;
2. WHERE: filter the rows;
3. GROUP BY: build the groups;
4. HAVING: filter the groups;
5. SELECT: compute the columns;
6. ORDER BY, then LIMIT.

Understanding this order explains most errors: for example, you cannot use a COUNT in the WHERE, because the groups do not exist yet at that point.

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

SELECT ville, COUNT(*) AS nb_clients
FROM clients
GROUP BY ville
ORDER BY nb_clients DESC;

SELECT categorie, COUNT(*) AS nb_produits, ROUND(AVG(prix), 2) AS prix_moyen
FROM produits
GROUP BY categorie;

SELECT strftime('%Y', date_commande) AS annee, COUNT(*) AS nb_commandes
FROM commandes
GROUP BY annee;

SELECT strftime('%Y', date_commande) AS annee, statut, COUNT(*) AS nb
FROM commandes
GROUP BY annee, statut
ORDER BY annee, nb DESC
LIMIT 8;

Common mistakes

SELECT ville, nom, COUNT(*) FROM clients GROUP BY ville;

SELECT ville, COUNT(*) FROM clients GROUP BY ville;

SELECT ville, COUNT(*) FROM clients;

SELECT ville, COUNT(*) FROM clients GROUP BY ville;

SELECT id, ville, COUNT(*) FROM clients GROUP BY id;

SELECT ville, COUNT(*) FROM clients GROUP BY ville;

Exercises

Try writing the query yourself before opening the solution.

SELECT departement, ROUND(AVG(salaire)) AS salaire_moyen FROM employes GROUP BY departement;

SELECT fournisseur_id, COUNT(*) AS nb FROM produits GROUP BY fournisseur_id;

Frequently asked questions