SQL Aggregate Functions 📊
Mentor's Note: Aggregates are the "Calculators" of SQL. Instead of looking at one row, they look at a whole column and return a single answer. "What is the total revenue?" or "Who is the oldest student?" 💡
🌟 The Scenario: The School Principal 🏫
Imagine you are the principal looking at the yearly exam results.
- COUNT: "How many students appeared for the exam?" 👥
- SUM: "What is the total of all marks collected?" ➕
- AVG: "What is the class average?" 📈
- MIN / MAX: "What were the lowest and highest scores?" 🏆
🎨 Visual Logic: Many to One
💻 1. The Core Functions
A. COUNT()
Counts the number of rows.
-- Scenario: Total number of employees
SELECT COUNT(*) FROM employees;
-- Scenario: Count rows where email is NOT NULL
SELECT COUNT(email) FROM employees;
B. SUM()
Calculates the total sum of a numeric column.
-- Scenario: Monthly payroll total
SELECT SUM(salary) FROM employees;
C. AVG()
Calculates the average value.
-- Scenario: Average salary in the company
SELECT AVG(salary) FROM employees;
D. MIN() & MAX()
Finds the lowest and highest values.
-- Scenario: Find the salary range
SELECT MIN(salary), MAX(salary) FROM employees;
⚠️ Important: Handling NULLs
Most aggregate functions ignore NULL values (except COUNT(*)).
- If you have 3 rows:
10,20, andNULL. AVGwill be(10 + 20) / 2 = 15. It ignores the NULL row entirely!
💡 Pro Tip
"You can use aggregates with Aliases to make your reports look professional!"
SELECT SUM(salary) AS Total_Payroll FROM employees;