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¶

graph TD
    A1[Row 1: 50] --> B{Aggregate}
    A2[Row 2: 70] --> B
    A3[Row 3: 90] --> B
    B -- "SUM" --> C[210]
    B -- "AVG" --> D[70]

💻 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, and NULL. - AVG will 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;