MySQL Chapter 9: Aggregate Functions

Chapter 9: Aggregate Functions

🎯 Goal

By the end of this chapter, you'll understand:

• What aggregate functions are

• Why they are used

• COUNT()

• SUM()

• AVG()

• MIN()

• MAX()

• How they work with WHERE

• How they work with GROUP BY

• Difference between normal queries and aggregate queries

---

Part 1 — The Story (Learn Like a Movie)

🎬 The King's Grand School

The king built the biggest school in the kingdom.

There were 100,000 students.

Every student had:

• Name

• Age

• Class

• Marks

The principal, Mr. Logic, had a magical register.

One day the king asked:

"How many students study in our school?"

Mr. Logic looked at every student...

"One..."

"Two..."

"Three..."

...

After 8 hours...

"100,000."

The king said,

"There has to be a smarter way!"

---

Enter the Wizard SQL

The wizard smiled.

He waved his wand.

SELECT COUNT(*) FROM students;

✨

Result:

100000

Done in milliseconds.

The king was amazed.

---

Next Question

The king asked:

"How many total marks have all students scored?"

Mr. Logic started adding.

92

+85

+78

+95

+...

His brain exploded.

---

Wizard:

SELECT SUM(marks)
FROM students;

✨

8452365

Done instantly.

---

The Third Question

The king asked:

"What is the average mark?"

Mr. Logic:

"Give me two days."

Wizard:

SELECT AVG(marks)
FROM students;

✨

84.52

---

Fourth Question

King:

"Who scored the highest marks?"

Mr. Logic:

"I'll check everyone."

Wizard:

SELECT MAX(marks)
FROM students;

✨

100

---

Fifth Question

King:

"Lowest marks?"

Wizard:

SELECT MIN(marks)
FROM students;

✨

18

---

The king laughed.

"So instead of checking every student manually, your magic summarizes everything?"

Wizard:

"Exactly."

---

What Actually Happened?

Aggregate functions don't return individual rows.

They summarize many rows into one answer.

Example

Students

Name	Marks
Rahul	90
Priya	80
John	95
Sara	85

Normal query

SELECT marks
FROM students;

Output

90
80
95
85

Aggregate query

SELECT AVG(marks)
FROM students;

Output

87.5

Notice:

Many rows → One result.

That's aggregation.

---

Think of It Like a Calculator

Imagine you have:

10
20
30
40
50

You ask:

"What is the total?"

Calculator returns

150

Database aggregate functions do the same thing.

---

The Five Most Important Aggregate Functions

---

1. COUNT()

Counts rows.

Table

Name
Rahul
Priya
John

Query

SELECT COUNT(*)
FROM students;

Output

3

Meaning:

"There are 3 students."

---

COUNT(column)

Table

Name	Phone
Rahul	999
Priya	NULL
John	888

Query

SELECT COUNT(phone)
FROM students;

Output

2

Why?

Because COUNT(column) ignores NULL.

---

COUNT(*) vs COUNT(column)

Table

ID	Phone
1	999
2	NULL
3	888

COUNT(*)

Returns

3

Because it counts every row.

---

COUNT(phone)

Returns

2

Because one phone is NULL.

---

2. SUM()

Adds numbers.

Sales table

Amount
100
250
300

Query

SELECT SUM(amount)
FROM sales;

Output

650

---

Real-life examples

• Total revenue

• Total salary

• Total expenses

• Total sales

• Total orders amount

---

3. AVG()

Calculates average.

Marks

80
90
70
60

Query

SELECT AVG(marks)
FROM students;

Output

75

---

Real-life examples

• Average salary

• Average rating

• Average marks

• Average order value

---

4. MAX()

Returns biggest value.

Prices

100
200
500
80

Query

SELECT MAX(price)
FROM products;

Output

500

---

Real-life examples

• Highest salary

• Most expensive product

• Highest marks

• Latest date

---

5. MIN()

Returns smallest value.

Prices

100
200
500
80

Query

SELECT MIN(price)
FROM products;

Output

80

---

Real-life examples

• Cheapest product

• Lowest salary

• Earliest date

• Youngest age

---

Aggregate Functions with WHERE

Table

Name	Marks
Rahul	90
Priya	60
John	80

Suppose the principal asks:

"What is the average of students scoring at least 80?"

SELECT AVG(marks)
FROM students
WHERE marks >= 80;

Output

85

The WHERE clause filters the rows before the aggregate function is applied.

---

Aggregate Functions with GROUP BY

Sales

City	Amount
Delhi	100
Delhi	200
Mumbai	300
Mumbai	500

Question

"What are total sales in each city?"

SELECT city,
       SUM(amount)
FROM sales
GROUP BY city;

Output

City	SUM
Delhi	300
Mumbai	800

Without GROUP BY, SUM() would have returned 1100 (one total for the entire table).

---

Aggregate Functions on Dates

Orders

Date
2024-01-01
2024-03-10
2024-02-15

SELECT MAX(order_date)
FROM orders;

Output

2024-03-10

Latest order.

---

SELECT MIN(order_date)
FROM orders;

Earliest order.

---

Can Aggregate Functions Work on Text?

Some can.

Example

COUNT(name)

Works.

But

SUM(name)

❌ Doesn't make sense.

Adding names isn't meaningful.

---

Common Mistakes

Mistake 1

SELECT SUM(name)

❌ Wrong

Names cannot be added.

---

Mistake 2

SELECT AVG(city)

❌ Wrong

Cities aren't numbers.

---

Mistake 3

Expecting multiple rows.

SELECT COUNT(*)
FROM students;

Returns only one row.

---

Real-Life Examples

Amazon

SELECT SUM(total_price)
FROM orders;

Total revenue.

---

YouTube

SELECT COUNT(*)
FROM videos;

Total videos.

---

Netflix

SELECT AVG(rating)
FROM movies;

Average rating.

---

Hospital

SELECT MAX(age)
FROM patients;

Oldest patient.

---

School

SELECT MIN(marks)
FROM students;

Lowest score.

---

Part 2 — Question & Answer (Progressive Learning)

Q1. What is an aggregate function?

Answer: An aggregate function summarizes data from multiple rows into a single result.

---

Q2. Why are aggregate functions used?

Answer: They help calculate summaries such as totals, averages, minimums, maximums, and counts without manually processing each row.

---

Q3. What does COUNT() do?

Answer: It counts rows. COUNT(*) counts all rows, while COUNT(column) counts only non-NULL values in that column.

---

Q4. Why do COUNT(*) and COUNT(column) sometimes return different answers?

Answer: COUNT(*) includes every row. COUNT(column) ignores rows where that column contains NULL.

---

Q5. What does SUM() do?

Answer: It adds all numeric values in a column and returns the total.

---

Q6. What does AVG() do?

Answer: It calculates the average of numeric values by adding them together and dividing by the number of non-NULL values.

---

Q7. What do MIN() and MAX() do?

Answer: MIN() returns the smallest value, while MAX() returns the largest value in a column.

---

Q8. Can aggregate functions work with WHERE?

Answer: Yes. WHERE filters the rows first, and then the aggregate function operates only on the remaining rows.

---

Q9. How do aggregate functions work with GROUP BY?

Answer: Instead of producing one summary for the whole table, they produce one summary for each group.

---

Q10. Give some real-life uses of aggregate functions.

Answer:

• Banking: Total balance deposited (SUM)

• E-commerce: Total orders (COUNT)

• Schools: Average marks (AVG)

• Hospitals: Oldest patient (MAX)

• Retail: Cheapest product (MIN)

---

Part 3 — Top 5 MCQs

1. Which function returns the total number of rows?

A. SUM()

B. AVG()

C. COUNT()

D. MAX()

✅ Answer: C

---

2. Which function returns the highest value?

A. MIN()

B. AVG()

C. MAX()

D. COUNT()

✅ Answer: C

---

3. What will this return?

SELECT SUM(price)
FROM products;

A. Highest price

B. Total of all prices

C. Average price

D. Number of products

✅ Answer: B

---

4. Which statement about COUNT(column) is correct?

A. It counts every row.

B. It ignores NULL values in that column.

C. It only counts duplicate values.

D. It returns the maximum value.

✅ Answer: B

---

5. Which query returns the average salary of employees earning more than 50,000?

A.

SELECT AVG(salary)
FROM employees
WHERE salary > 50000;

B.

SELECT SUM(salary)
FROM employees;

C.

SELECT COUNT(salary)
FROM employees;

D.

SELECT MAX(salary)
FROM employees;

✅ Answer: A

---

🧠 Chapter 9 Summary

• Aggregate functions summarize multiple rows into one result.

• The five core aggregate functions are:

  • COUNT() → Counts rows or non-NULL values.

  • SUM() → Adds numeric values.

  • AVG() → Calculates the average.

  • MIN() → Finds the smallest value.

  • MAX() → Finds the largest value.

• WHERE filters rows before aggregation.

• GROUP BY creates separate summaries for each group instead of one summary for the whole table.

Rule to remember:
Normal SELECT shows individual rows. Aggregate functions show summarized information.