An aggregate function turns many rows into one value. Filtering decides which rows, or which groups, are allowed to count.
This lesson is part of database development. For deeper predictions with NULL and empty groups, continue later to SQL reasoning in a fictional database.
Which aggregate does the question want?
Read the question for a clue word, then match it to a function.
| The question says | Use | Returns |
|---|---|---|
| How many rows, how many sales | COUNT(*) | A count of rows |
| Total, altogether | SUM(column) | The added values |
| Average, mean | AVG(column) | The mean value |
| Highest, most expensive | MAX(column) | The largest value |
| Lowest, cheapest | MIN(column) | The smallest value |
Here is an original table for a small shop called Kedai Mawar. It is named Sales, and Amount is in RM.
| SaleID | Product | Category | Qty | Amount |
|---|---|---|---|---|
| 1 | Pen | Stationery | 10 | 25.00 |
| 2 | Ruler | Stationery | 4 | 8.00 |
| 3 | Rice 5kg | Grocery | 2 | 36.00 |
| 4 | Sugar | Grocery | 3 | 12.00 |
| 5 | Notebook | Stationery | 6 | 18.00 |
| 6 | Cooking oil | Grocery | 1 | 9.50 |
| 7 | Marker | Stationery | 2 | 7.00 |
Worked example: totals per category
SELECT Category, SUM(Amount) FROM Sales GROUP BY Category gives one row for each category.
- Stationery: 25.00 + 8.00 + 18.00 + 7.00 = RM58.00
- Grocery: 36.00 + 12.00 + 9.50 = RM57.50
The average Stationery sale is 58.00 ÷ 4 = RM14.50, and the highest is RM25.00.
Does WHERE or HAVING come first?
WHERE comes first. It removes rows before the groups are formed, then the groups are totalled, and only then does HAVING remove groups.
Add WHERE Qty >= 2 to the totals query. The Cooking oil row (Qty 1) is removed, so Grocery becomes 36.00 + 12.00 = RM48.00. Every Stationery row has Qty of 2 or more, so Stationery stays at RM58.00.
Now add HAVING SUM(Amount) > 50 as well. Stationery (58.00) passes and Grocery (48.00) fails, so only Stationery remains.
Without the WHERE, the same HAVING would keep both groups, because Grocery would be RM57.50. The two filters act at different moments, which is why they give different results.
The mistake: a column that is not grouped
A student writes SELECT Product, Category, SUM(Amount) FROM Sales GROUP BY Category. The Stationery group holds four products, and SQL has no way to choose one. The fix is to select only Category and the aggregate.
Another slip is writing WHERE SUM(Amount) > 50. WHERE cannot see a total, because no group has been formed yet.
| Wrong | Why | Right |
|---|---|---|
| WHERE SUM(Amount) > 50 | Groups do not exist at WHERE | HAVING SUM(Amount) > 50 |
| SELECT Product … GROUP BY Category | Product is not one value per group | Remove Product, or group by it too |
| HAVING Qty >= 2 | A row-level test belongs earlier | WHERE Qty >= 2 |
Check yourself
What does this query return?
SELECT Category, COUNT() FROM Sales WHERE Amount > 8 GROUP BY Category HAVING COUNT() >= 3
Answer
WHERE keeps rows with Amount above 8: Pen (25.00), Rice (36.00), Sugar (12.00), Notebook (18.00) and Cooking oil (9.50). Ruler (8.00) is not above 8, and Marker (7.00) fails.
Grouping gives Stationery with 2 rows (Pen, Notebook) and Grocery with 3 rows (Rice, Sugar, Cooking oil).
HAVING COUNT(*) >= 3 keeps only Grocery. The result is one row: Grocery, 3.
What to study next
Aggregates change when tables are joined, because a join can repeat rows. Read joining related tables, then test yourself with the database development practice set.
The SQL reasoning sandbox lets you check results on your own rows. If you want a teacher to work through WHERE and HAVING with you, see online one-to-one Computer Science tuition.