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.
Put this guide into practice.
Open the related exercise, run your query and compare the expected result.