What are Aggregate Queries?
Sample Students Table
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 19 | A |
| 3 | Peter | 21 | A |
| 4 | Sarah | 20 | B |
| 5 | Tom | 22 | B |
5 rows
Aggregate queries combine GROUP BY with multiple functions to answer several questions at once.
Think of it like: Instead of asking "How many?" separately from "What's the average?", you ask both in ONE query!
Combining Multiple Functions
Sample Students Table
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 19 | A |
| 3 | Peter | 21 | A |
| 4 | Sarah | 20 | B |
| 5 | Tom | 22 | B |
5 rows
You can use COUNT, SUM, AVG, MIN, MAX all together:
SELECT
grade,
COUNT(*) AS total_students,
MIN(age) AS youngest,
MAX(age) AS oldest,
AVG(age) AS avg_age
FROM students
GROUP BY grade;This ONE query shows:
- How many students in each grade (COUNT)
- Youngest student age (MIN)
- Oldest student age (MAX)
- Average age (AVG)
Real Example: Product Analysis
Sample Students Table
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 19 | A |
| 3 | Peter | 21 | A |
| 4 | Sarah | 20 | B |
| 5 | Tom | 22 | B |
5 rows
SELECT
category,
COUNT(*) AS products,
MIN(price) AS cheapest,
MAX(price) AS most_expensive,
AVG(price) AS avg_price
FROM products
GROUP BY category;Shows complete price information for each category!
Using WHERE and HAVING Together
Sample Students Table
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 19 | A |
| 3 | Peter | 21 | A |
| 4 | Sarah | 20 | B |
| 5 | Tom | 22 | B |
5 rows
SELECT
department,
COUNT(*) AS employees,
AVG(salary) AS avg_salary
FROM employees
WHERE is_active = TRUE
GROUP BY department
HAVING COUNT(*) >= 5;Simple explanation:
- WHERE picks active employees only
- GROUP BY groups by department
- HAVING shows departments with 5+ employees
Summary
Sample Students Table
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 19 | A |
| 3 | Peter | 21 | A |
| 4 | Sarah | 20 | B |
| 5 | Tom | 22 | B |
5 rows
- Combine multiple aggregates in one query
- Use COUNT, SUM, AVG, MIN, MAX together
- Add WHERE to filter rows first
- Add HAVING to filter groups
- Perfect for complete data analysis!
SQL Editor
Loading...
Multiple aggregates in one query
Output
Click "Run Query" to see results
SQL Editor
Loading...
Using WHERE and HAVING together
Output
Click "Run Query" to see results