SQL COUNT, SUM, AVG, MIN and MAX

How many orders have we received? What is the average price of our products? What is the highest salary? Aggregate functions answer this kind of question: they take all the rows of a column and summarize them into a single value.

Syntax

SELECT COUNT(*), SUM(column), AVG(column), MIN(column), MAX(column)
FROM table;

How it works

Without GROUP BY, a query that uses an aggregate returns a single row for the whole table. To get one result per category, per city or per month, you need to add a GROUP BY (see the next lesson).

Averages often have many decimals: ROUND(AVG(prix), 2) rounds to two decimal places.

You can also combine an aggregate with a WHERE: the filter is applied before the calculation.

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 COUNT(*) AS nb_commandes
FROM commandes;

SELECT MIN(prix) AS min, MAX(prix) AS max, ROUND(AVG(prix), 2) AS moyenne
FROM produits;

SELECT ROUND(SUM(quantite * prix_unitaire), 2) AS chiffre_affaires
FROM lignes_commande;

SELECT COUNT(*) AS annulees, MIN(date_commande) AS premiere
FROM commandes
WHERE statut = 'annulée';

Common mistakes

SELECT nom, SUM(stock) FROM produits;

SELECT categorie, SUM(stock) FROM produits GROUP BY categorie;

SELECT categorie FROM produits WHERE COUNT(*) > 5 GROUP BY categorie;

SELECT categorie FROM produits GROUP BY categorie HAVING COUNT(*) > 5;

SELECT AVG(salaire) FROM employes;

SELECT ROUND(AVG(salaire), 2) FROM employes;

Exercises

Try writing the query yourself before opening the solution.

SELECT SUM(salaire) AS masse_salariale, ROUND(AVG(salaire)) AS salaire_moyen FROM employes;

SELECT MIN(date_commande) AS premiere, MAX(date_commande) AS derniere FROM commandes;

Frequently asked questions