Aggregate Functions - Analyzing Your Data

Up until now, you've been working with individual rows: "Show me Alice's GPA" or "Find students in Biology." But what if you want to answer bigger questions like "What's the average GPA across all students?" or "How many students are enrolled?" That's where aggregate functions come in! They take many rows and collapse them into a single summary value. Think of them as your data analysis powerhouse!

📚 What You'll Learn

• What aggregate functions are and why they're essential
• The "Big Five" aggregate functions: COUNT, SUM, AVG, MIN, MAX
• How aggregates collapse many rows into single summary values
• The critical difference between COUNT(*) and COUNT(column)
• How NULL values affect each aggregate function
• Why you can't mix aggregate and non-aggregate columns (without GROUP BY)
• Using aggregates with WHERE to filter before summarizing
• Combining multiple aggregates in one query
• Real-world business analytics scenarios
• Common beginner mistakes and how to avoid them
• Hands-on practice exercises

1. What Are Aggregate Functions?

Aggregate functions perform calculations across multiple rows and return a single result. They "aggregate" (combine) data into summaries.

Visual Example: From Many Rows to One Summary

SELECT AVG(gpa) 
FROM students;

See the magic? Four rows collapsed into one number: the average GPA!

2. The Big Five Aggregate Functions

SQL has five essential aggregate functions you'll use constantly:

3. COUNT() - How Many Rows?

COUNT() is the most commonly used aggregate function. It counts rows.

COUNT(*) - Count All Rows

-- How many students are in the database?
SELECT COUNT(*) AS total_students
FROM students;

Result: If there are 150 students, you get: total_students: 150

COUNT(column) - Count Non-NULL Values

-- How many students provided a phone number?
SELECT COUNT(phone) AS students_with_phone
FROM students;

This counts only rows where phone is NOT NULL.

The Critical Difference

SELECT COUNT(*) 
FROM students;

SELECT COUNT(phone) 
FROM students;

COUNT(DISTINCT column) - Count Unique Values

-- How many different majors do we have?
SELECT COUNT(DISTINCT major) AS unique_majors
FROM students;

If 100 students are divided among 5 majors, this returns 5 (not 100).

4. SUM() - What's the Total?

SUM() adds up all values in a numeric column.

-- What are our total sales?
SELECT SUM(sales_amount) AS total_sales
FROM orders;

Example with Sample Data:

SELECT SUM(amount) 
FROM orders;

5. AVG() - What's the Average?

AVG() calculates the average (mean) of numeric values.

-- What's the average GPA?
SELECT AVG(gpa) AS average_gpa
FROM students;

Example Calculation:

How NULL Affects AVG()

AVG() ignores NULL values completely:

6. MIN() and MAX() - Finding Extremes

MIN() finds the smallest value, MAX() finds the largest value.

Finding Minimum Values

-- What's the lowest GPA?
SELECT MIN(gpa) AS lowest_gpa
FROM students;

Finding Maximum Values

-- What's the highest price?
SELECT MAX(price) AS highest_price
FROM products;

MIN/MAX Work with Text Too!

-- First student alphabetically
SELECT MIN(name) AS first_name_alphabetically
FROM students;

-- Last student alphabetically
SELECT MAX(name) AS last_name_alphabetically
FROM students;

With names like Alice, Bob, Carol, Diana:
MIN(name) = "Alice" (first alphabetically)
MAX(name) = "Diana" (last alphabetically)

7. Combining Multiple Aggregates in One Query

You can use multiple aggregate functions in a single SELECT statement:

-- Get complete GPA statistics
SELECT 
    COUNT(*) AS total_students,
    AVG(gpa) AS average_gpa,
    MIN(gpa) AS lowest_gpa,
    MAX(gpa) AS highest_gpa
FROM students;

Result:

One query, complete statistical summary! This is incredibly powerful for dashboards and reports.

8. Using WHERE to Filter Before Aggregating

WHERE filters rows before the aggregate function runs:

-- Average GPA of Biology majors only
SELECT AVG(gpa) AS bio_average_gpa
FROM students
WHERE major = 'Biology';

Execution Order:

Practical Example: Sales Analysis

-- Total sales for 2024 only
SELECT 
    COUNT(*) AS orders_in_2024,
    SUM(amount) AS total_revenue
FROM orders
WHERE YEAR(order_date) = 2024;

9. The Mixing Problem (Without GROUP BY)

This is one of the most confusing things for beginners:

-- ❌ ERROR: Can't mix aggregate and non-aggregate columns!
SELECT name, AVG(gpa)
FROM students;

Think About It Logically:

The Solution: GROUP BY (Next Lesson!)

To mix aggregates with regular columns, you need GROUP BY. That's coming in the next lesson! For now, remember:

• ✅ Aggregates alone: SELECT AVG(gpa) FROM students
• ✅ Regular columns alone: SELECT name FROM students
• ❌ Mixed without GROUP BY: SELECT name, AVG(gpa) FROM students

10. Common Beginner Mistakes

Mistake #1: Confusing COUNT(*) and COUNT(column)

-- These give DIFFERENT results!
SELECT COUNT(*) FROM students;     -- Counts all rows (4)
SELECT COUNT(phone) FROM students; -- Counts non-NULL phones (3)

Mistake #2: Using AVG() on Text

-- ❌ ERROR: Can't average text!
SELECT AVG(name) FROM students;

-- ✅ CORRECT: Average numeric values
SELECT AVG(gpa) FROM students;

Mistake #3: Forgetting NULL Affects Results

-- If there are 100 students but 20 have NULL GPA:
SELECT AVG(gpa) FROM students;
-- Averages only the 80 non-NULL GPAs (not 100!)

Mistake #4: Mixing Aggregates with Regular Columns

-- ❌ WRONG: Can't mix without GROUP BY
SELECT name, AVG(gpa) FROM students;

-- ✅ CORRECT: Just the aggregate
SELECT AVG(gpa) FROM students;

Mistake #5: Using Aggregate in WHERE

-- ❌ WRONG: Can't use aggregate in WHERE
SELECT * FROM students WHERE gpa > AVG(gpa);

-- ✅ CORRECT: Use HAVING (covered in next lesson)
-- Or calculate separately in subquery

11. 🎯 Hands-On Practice Exercises

SELECT COUNT(*) AS total_students
FROM students;

SELECT AVG(gpa) AS average_gpa
FROM students;

SELECT 
    MAX(gpa) AS highest_gpa,
    MIN(gpa) AS lowest_gpa
FROM students;

SELECT COUNT(email) AS students_with_email
FROM students;

SELECT 
    COUNT(*) AS total_students,
    AVG(gpa) AS average_gpa,
    MIN(gpa) AS lowest_gpa,
    MAX(gpa) AS highest_gpa
FROM students;

SELECT AVG(gpa) AS cs_average_gpa
FROM students
WHERE major = 'Computer Science';

SELECT 
    COUNT(*) AS total_students,
    COUNT(phone) AS students_with_phone,
    COUNT(*) - COUNT(phone) AS students_without_phone
FROM students;

📝 Key Takeaways

• Aggregate functions collapse many rows into one summary
• COUNT(*) counts all rows, COUNT(column) counts non-NULL values
• SUM() adds numbers, AVG() calculates average
• MIN() finds smallest, MAX() finds largest
• All aggregates (except COUNT(*)) ignore NULL values
• You can combine multiple aggregates in one query
• Use WHERE to filter before aggregating
• Can't mix aggregates with regular columns without GROUP BY
• Aggregates work on numbers, but MIN/MAX also work on text
• Perfect for dashboards, reports, and data analysis!