Sorting and Aggregation
SQL Basics Course
Topic 10: Sorting and Aggregation
Learn how to sort records and generate summaries using SQL aggregate functions.
Learning Objectives
- Sort records using ORDER BY
- Use ASC and DESC ordering
- Count records
- Calculate totals and averages
- Find minimum and maximum values
- Create basic reports
ORDER BY Clause
ORDER BY sorts query results.
SELECT * FROM students
ORDER BY name;
Ascending Order (ASC)
SELECT * FROM students
ORDER BY name ASC;
ASC sorts from A to Z or smallest to largest.
Descending Order (DESC)
SELECT * FROM students
ORDER BY name DESC;
DESC sorts from Z to A or largest to smallest.
COUNT()
SELECT COUNT(*) FROM students;
Returns the number of records.
SUM()
SELECT SUM(salary)
FROM employees;
Adds all values in a column.
AVG()
SELECT AVG(salary)
FROM employees;
Calculates the average value.
MIN()
SELECT MIN(salary)
FROM employees;
Returns the smallest value.
MAX()
SELECT MAX(salary)
FROM employees;
Returns the largest value.
Example Employee Table
| ID | Name | Salary |
|---|---|---|
| 1 | John | 50000 |
| 2 | Sarah | 60000 |
| 3 | Mike | 45000 |
Business Report Example
SELECT
COUNT(*) AS Total_Employees,
AVG(salary) AS Average_Salary,
MAX(salary) AS Highest_Salary,
MIN(salary) AS Lowest_Salary
FROM employees;
When to Use Aggregate Functions
- Sales reports
- Employee analytics
- Student performance summaries
- Financial dashboards
- Inventory management
Best Practices
- Use aliases for readability.
- Sort only when needed.
- Aggregate large datasets carefully.
- Verify calculations.
- Combine aggregates with filters when required.
Practice Exercises
- Count all students.
- Find the average salary.
- Find the highest salary.
- Sort employees by salary descending.
- Sort students alphabetically.
Quiz
1. Which clause sorts data?
2. What does COUNT() return?
3. What is the purpose of AVG()?
4. Difference between ASC and DESC?
5. Which function returns the highest value?
Summary
- ORDER BY sorts records.
- ASC sorts ascending.
- DESC sorts descending.
- COUNT(), SUM(), AVG(), MIN(), and MAX() summarize data.
- Aggregate functions are essential for reporting.