Use aggregate functions: COUNT, SUM, AVG, MIN, MAX
Group rows by a column with GROUP BY
Filter grouped results with HAVING
Understand the difference between WHERE and HAVING
📊 Aggregate Query Engine
Run aggregate and GROUP BY queries on the students table. Click a preset to see how each aggregate function works.
Aggregate Functions
Aggregate functions collapse multiple rows into a single value. They operate on a set of rows and return one number. They are used for reporting, dashboards, and analytics.
COUNT(*) — count all rows. COUNT(column) — count non-NULL values.
SUM(score) — total of all scores. AVG(score) — arithmetic mean.
GROUP BY divides rows into groups based on a column, then applies the aggregate function to each group separately. The result has one row per group.
SELECT city, COUNT(*) FROM students GROUP BY city — count students per city.
Every column in SELECT that is NOT an aggregate function must appear in GROUP BY.
HAVING vs WHERE
WHERE filters individual rows before grouping. HAVING filters groups after grouping. Use HAVING to filter based on aggregate results: HAVING AVG(score) > 80.
Quick Check
1. What does COUNT(*) return?
The sum of all values
The number of rows
The maximum value
The average value
2. When using GROUP BY city, what does each row in the result represent?
A single student
One city, with aggregate values calculated for all students in that city
The first student in each city
All cities concatenated into one row
3. What is the difference between WHERE and HAVING?
HAVING only works with COUNT; WHERE works with all conditions
WHERE filters rows before grouping; HAVING filters groups after aggregation
They are identical in behavior
WHERE comes after GROUP BY; HAVING comes before
4. In SELECT city, AVG(score) FROM students GROUP BY city, why must city appear in GROUP BY?
It is a SQL requirement with no logical reason
Because city is not an aggregate — SQL needs to know how to group the rows, and every non-aggregate column defines the grouping