Aggregates and Grouping

Aggregates turn many rows into one number. Grouping lets you produce one number per category.

Aggregate Functions

An aggregate takes many rows and returns one value.

SELECT COUNT(*) AS total_students,
       AVG(score) AS average_score,
       MAX(score) AS best_score
FROM students;

GROUP BY

Group rows that share a value, then aggregate inside each group. Every column in the SELECT that is not aggregated should appear in GROUP BY.

SELECT level,
       COUNT(*) AS students,
       ROUND(AVG(score), 1) AS average_score
FROM students
GROUP BY level
ORDER BY level;

HAVING

WHERE filters rows before grouping. HAVING filters groups after aggregation.

SELECT level, AVG(score) AS average_score
FROM students
GROUP BY level
HAVING AVG(score) >= 65;

Practice

1. Count students in each level.
2. Find the highest score in SS2.
3. Show levels where the average score is below 50.

Hands-on exercise

Given a sales table with product and amount, write a query that returns each product, the number of sales and the total amount. Include only products with a total above 1000, sorted by total descending.

Recap

Aggregates summarise rows, GROUP BY creates one summary per category, and HAVING filters those summaries.