Sorting & Aggregation in MySQL
So far, you have learned how to:
- Insert data
- Retrieve data using SELECT
- Filter data using WHERE and operators
Now we move one step ahead.
In real-world databases, we often need to:
- Sort data
- Remove duplicates
- Calculate totals or averages
- Group data into categories
This is where sorting and aggregation come in.
All examples below work in MySQL.
1. ORDER BY – Sorting Data
The ORDER BY clause is used to sort query results.
Syntax
SELECT column_name
FROM table_name
ORDER BY column_name;
By default, sorting is ascending (ASC).
Example: Sort by Age (Ascending)
SELECT *
FROM Students
ORDER BY age;
Descending Order
SELECT *
FROM Students
ORDER BY age DESC;
Sorting by Multiple Columns
SELECT *
FROM Students
ORDER BY city ASC, age DESC;
First sorted by city, then by age within each city.
2. DISTINCT – Removing Duplicate Values
The DISTINCT keyword removes duplicate records from the result.
Example
SELECT DISTINCT city
FROM Students;
If many students belong to the same city, each city will appear only once.
Without DISTINCT:
Duplicates appear.
With DISTINCT:
Unique values appear.
3. Aggregate Functions
Aggregate functions perform calculations on multiple rows and return a single result.
| Function | Purpose |
| COUNT() | Counts rows |
| SUM() | Adds values |
| AVG() | Calculates average |
| MIN() | Finds minimum |
| MAX() | Finds maximum |
COUNT()
Counts the number of rows.
SELECT COUNT(*)
FROM Students;
Counts total students.
SUM()
Adds numeric values.
SELECT SUM(salary)
FROM Employees;
AVG()
Finds the average value.
SELECT AVG(age)
FROM Students;
MIN() and MAX()
SELECT MIN(age), MAX(age)
FROM Students;
Returns the smallest and largest age.
4. GROUP BY – Grouping Data
GROUP BY groups rows that have the same values.
Example
SELECT city, COUNT(*)
FROM Students
GROUP BY city;
This shows:
- Each city
- Number of students in that city
Without GROUP BY:
COUNT() gives the total count.
With GROUP BY:
COUNT() gives the count per group.
5. HAVING Clause – Filtering Groups
The HAVING clause filters grouped results.
Important difference:
- WHERE filters rows before grouping
- HAVING filter groups after grouping
Example
SELECT city, COUNT(*) AS total_students
FROM Students
GROUP BY city
HAVING total_students > 5;
This shows only cities with more than 5 students.
You cannot use WHERE with aggregate functions.
❌ Wrong:
WHERE COUNT(*) > 5;
✅ Correct:
Use HAVING.
6. WHERE vs HAVING (Important Concept)
| WHERE | HAVING |
| Filters rows | Filters groups |
| Used before GROUP BY | Used after GROUP BY |
| Cannot use aggregate functions | Can use aggregate functions |
Example Combining Everything
SELECT city, AVG(age) AS avg_age
FROM Students
WHERE age > 18
GROUP BY city
HAVING avg_age > 20
ORDER BY avg_age DESC;
Execution order:
- WHERE filters rows
- GROUP BY groups rows
- HAVING filters groups
- ORDER BY sorts results
Common Beginner Pitfalls
- Using WHERE with aggregate functions
Use HAVING instead. - Forgetting GROUP BY when selecting non-aggregated columns
❌ Wrong:
SELECT city, COUNT(*)
FROM Students;
✅ Correct:
SELECT city, COUNT(*)
FROM Students
GROUP BY city;
- Forgetting ASC/DESC in sorting
- Confusing DISTINCT with GROUP BY
DISTINCT removes duplicates
GROUP BY groups data for aggregation
Final Summary
In this article, you learned:
- ORDER BY → Sort results
- DISTINCT → Remove duplicates
- Aggregate functions → Analyze data
- GROUP BY → Group rows
- HAVING → Filter grouped results