GROUP BY and aggregate functions
Calculate metrics from multiple rows.
Intermediate
SQL syntax
Finance and accounting scenario
Related exercises
Objective
Produce totals, counts, averages and finance metrics by customer, month, supplier or product.
Exercise context
The dataset contains row-level detail. Aggregations turn it into summaries such as revenue by customer, VAT by month, purchases by supplier or margin by product.
Key concepts
- SUM adds numeric values.
- COUNT counts rows.
- AVG calculates an average.
- GROUP BY defines the grouping level.
- Every non-aggregated column in SELECT should generally appear in GROUP BY.
Syntax pattern
SELECT colonne_regroupement, SUM(montant) AS total
FROM table
GROUP BY colonne_regroupement;Applied example
SELECT c.nom_client, SUM(f.montant_ht) AS ca_ht
FROM clients c
JOIN factures f ON c.id_client = f.id_client
GROUP BY c.nom_client;Common mistakes
- Returning a non-aggregated column without adding it to GROUP BY.
- Confusing WHERE and HAVING.
- Adding gross amounts when the analysis requires net amounts.
- Forgetting the alias for a calculated metric.