Is the condition on a line or on a group?
A status or date belongs to a source line: use WHERE. A total, average or number of orders exists after grouping: use HAVING.
The order modifies the meaning. Excluding rows canceled before SUM is not equivalent to calculating all statuses then hiding certain groups.
The query explained
Start by defining what a row of the result should represent. This decision determines the necessary columns, groupings, and controls.
The following example isolates the main mechanism. Run it, observe the result, then change one clause at a time to understand its effect.
SELECT id_client, COUNT(*) AS commandes_payees
FROM commandes
WHERE statut = 'Paid'
GROUP BY id_client
HAVING COUNT(*) >= 2;Read the result like an analyst
WHERE only prepares paid orders. GROUP BY creates one row per customer, then HAVING maintains volumes of at least two.
Mentally separate these steps and monitor the number of records returned at each when the query becomes complex.
- Formulate the perimeter of lines.
- Set the level of detail for groups.
- Apply threshold on aggregation.
- Compare the overall total to the sum of the groups.
Errors that distort the analysis
A query can be valid without correctly answering the question. The most costly errors are often silent: wrong perimeter, multiplied line, or missing value interpreted as zero.
Before sharing the result, compare it to a small hand-calculated sample and keep the scope definition with the query.
- Write WHERE COUNT(*).
- Filter amount >= threshold instead of SUM(amount) >= threshold.
- Forget GROUP BY.
- Compare groups constructed with different perimeters.
Get into practice
Do exercise HAVING on the average basket, then change the threshold to observe the retained groups.
The SQL simulator of SQL.tn only runs read queries on dummy data in your browser. You can try multiple writes and compare their results without installing any software.
Test the difference on concrete data
Who has paid more than €100 in total? Emma paid €60 and then €70. James paid €80 and also has a cancelled €100 order. Olivia paid exactly €100.
| customer_name | amount | status |
|---|---|---|
| Emma | 60 | paid |
| Emma | 70 | paid |
| James | 80 | paid |
| James | 100 | cancelled |
| Olivia | 100 | paid |
SELECT customer_name, SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_name
HAVING SUM(amount) > 100
ORDER BY customer_name;WHERE removes the cancelled order before calculation. GROUP BY combines purchases by customer. HAVING keeps totals strictly above €100: only Emma remains, with €130.
Try replacing HAVING with AND amount > 100 in WHERE: no rows remain. You filtered individual orders, not totals. Using >= 100 in HAVING would also include Olivia.
Browser-based SQL environment
Test the difference on concrete data
Run a read-only query on this exercise's fictional data. Nothing is sent to SQL.tn.