GROUP BY and HAVING: building KPIs in SQL

Moving from detail to an indicator

An order table typically contains one row per transaction. A dashboard instead expects one line per city, per category or per month, accompanied by indicators such as revenue and average basket.

GROUP BY changes the level of detail of the result. All rows sharing the same value are grouped together before calculating the aggregation functions.

COUNT, SUM and AVG answer three different questions

COUNT counts, SUM adds, and AVG calculates an average. These functions collapse multiple rows into a synthetic value.

In a sales report, you can count orders, add their amount, and calculate the average order value in the same query.

• COUNT(*) counts the rows in the group.
• COUNT(colonne) ignores the NULL values in this column.
• SUM(montant) adds the amounts not NULL.
• AVG(montant) calculates their average.

SELECT
  COUNT(*) AS nombre_commandes,
  SUM(montant) AS chiffre_affaires,
  ROUND(AVG(montant), 2) AS panier_moyen
FROM commandes;

GROUP BY creates one line per group

To get one row per city, add ville to the result and the GROUP BY clause. Each aggregation is then calculated separately for Tunis, Sfax and the other cities present.

As a practical rule, any column displayed in SELECT that is not aggregated should appear in GROUP BY.

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

WHERE filters rows before calculation

WHERE removes rows before forming groups. To build the KPIs for the year 2026, filter the orders by their date before executing GROUP BY.

This distinction is important: the lines excluded by WHERE do not participate in the counting, the sum, or the average.

SELECT
  id_client,
  SUM(montant) AS chiffre_affaires
FROM commandes
WHERE date_commande >= DATE '2026-01-01'
  AND date_commande < DATE '2027-01-01'
GROUP BY id_client;

HAVING filters groups after calculation

HAVING occurs after aggregation. For example, it allows you to keep only cities with at least five orders or a revenue greater than a threshold.

Use WHERE for a condition on an original row and HAVING for a condition on a group or aggregation.

SELECT
  id_client,
  COUNT(*) AS nombre_commandes
FROM commandes
GROUP BY id_client
HAVING COUNT(*) >= 5
ORDER BY nombre_commandes DESC;

Assemble a Complete KPI Query

To analyze sales by city, you must first link orders to customers, filter the period, group by city then filter the unrepresentative groups.

The chiffre_affaires alias can be reused in ORDER BY with the site simulator. The HAVING clause retains the full aggregation expression to remain explicit.

SELECT
  c.ville,
  COUNT(co.id_commande) AS nombre_commandes,
  SUM(co.montant) AS chiffre_affaires,
  ROUND(AVG(co.montant), 2) AS panier_moyen
FROM clients AS c
INNER JOIN commandes AS co
  ON c.id_client = co.id_client
WHERE co.date_commande >= DATE '2026-01-01'
  AND co.date_commande < DATE '2027-01-01'
GROUP BY c.ville
HAVING COUNT(co.id_commande) >= 5
ORDER BY chiffre_affaires DESC;

Errors that distort a KPI

Correct aggregation depends as much on the level of detail as on the formula. Before publishing an indicator, check what exactly each line in the result represents.

• Show a non-aggregated column without adding it to GROUP BY.
• Use WHERE COUNT(*) > 5 instead of HAVING.
• Count after a join which has multiplied the lines.
• Forget that COUNT(colonne) ignores NULL values.
• Mix different periods in the same indicator.

Your checklist before sharing the result

Set the expected level — one row per city, customer or month — then choose the aggregations. Apply the period filter with WHERE, the business threshold with HAVING and a readable sort with ORDER BY.

Finally, check a few groups by hand and document the KPI definition. A short but ambiguous query remains less useful than an explicit and reproducible calculation.