Chapter 8: Aggregate Functions ⭐⭐
🎯 Goal
By the end of this chapter, you'll understand:
What aggregate functions are
Why they are used
The five most important aggregate functions:
COUNT()SUM()AVG()MAX()MIN()
Difference between row functions and aggregate functions
How aggregate functions work with
GROUP BYReal-life examples
Common beginner mistakes
Part 1 — The Story (Learn Like a Movie)
🎬 The King's Treasure Room 👑💰
In the kingdom of SQL Land, there was a massive treasure vault.
Every treasure chest looked like this:
| Chest | Gold ($) |
|---|---|
| 1 | $500 |
| 2 | $800 |
| 3 | $300 |
| 4 | $700 |
| 5 | $900 |
One morning...
The king asked his accountant, Bob,
"How much gold do we have?"
Bob sighed.
He started counting...
$500...
+$800...
+$300...
+$700...
+$900...
Finally...
"$3,200, Your Majesty!"
The king smiled.
Next day...
The king asked,
"How many treasure chests do we own?"
Bob counted again.
1...
2...
3...
4...
5...
"Five chests."
Next day...
"What's the average gold per chest?"
Bob again...
($500+$800+$300+$700+$900)
÷5
= $640
Next day...
"Which chest has the most gold?"
Bob searched every chest.
Maximum
↓
$900
Next day...
"Which chest has the least gold?"
Again...
Minimum
↓
$300
Bob was exhausted.
Every day...
Same work.
Different question.
Then...
A mysterious wizard arrived.
His name was...
🧙 Sir Aggregate
He said,
"Why are you doing everything manually?"
"I have five magical spells."
The king became curious.
Spell 1
COUNT()
The wizard snapped his fingers.
"There are 5 treasure chests."
Done instantly.
Bob's jaw dropped.
Spell 2
SUM()
Another snap.
"Total gold = $3,200."
Done.
Spell 3
AVG()
Another spell.
"Average = $640."
Done.
Spell 4
MAX()
Highest treasure
↓
$900
Done.
Spell 5
MIN()
Smallest treasure
↓
$300
Done.
The king asked,
"How did you answer so quickly?"
The wizard smiled.
"I don't look at each chest individually."
"I treat all the chests as one collection."
Bob looked confused.
Wizard explained.
Imagine...
Five students.
90
85
70
60
95
Instead of asking,
"What is Rahul's mark?"
You ask,
"What is the average mark of the entire class?"
You're asking about the whole group, not one student.
That's exactly what aggregate functions do.
The Festival Continues 🎉
The king organized a food festival.
Sales
| Seller | Sales ($) |
|---|---|
| Amit | $200 |
| Rahul | $300 |
| Amit | $400 |
| Rahul | $100 |
| Priya | $500 |
The king asked,
"How much did each seller earn?"
Sir Aggregate smiled.
"First, ask Captain GROUP BY to create teams."
Amit Group
↓
$200
$400
Rahul Group
↓
$300
$100
Priya Group
↓
$500
Then Sir Aggregate used
SUM()
Result
| Seller | Total Sales |
|---|---|
| Amit | $600 |
| Rahul | $400 |
| Priya | $500 |
The king realized...
Captain GROUP BY creates teams.
Sir Aggregate calculates for each team.
They're best friends.
Moral of the Story
Aggregate functions don't work on one row.
They work on many rows together and return one summarized value.
What is an Aggregate Function?
An aggregate function performs a calculation on a collection of rows and returns a single result.
Think of it like asking:
Total salary?
Average age?
Highest marks?
Lowest temperature?
Number of employees?
These are questions about the whole collection, not individual rows.
Imagine Amazon
Orders
| Customer | Amount ($) |
|---|---|
| Rahul | 200 |
| Priya | 500 |
| Rahul | 300 |
| John | 100 |
CEO asks
Total revenue?
Use
SUM()
Number of orders?
COUNT()
Average order value?
AVG()
Highest order?
MAX()
Lowest order?
MIN()
The Five Most Important Aggregate Functions
1. COUNT()
Counts rows.
Students
| Name |
|---|
| Rahul |
| Priya |
| John |
COUNT()
↓
3
Real-life examples
Number of users
Number of products
Number of orders
Number of employees
2. SUM()
Adds values together.
Sales
| Amount |
|---|
| 100 |
| 200 |
| 300 |
SUM()
↓
600
Used for
Revenue
Salary
Expenses
Profit
Total marks
3. AVG()
Calculates average.
Marks
90
80
70
AVG()
↓
80
Used for
Average salary
Average age
Average order value
Average rating
4. MAX()
Largest value.
Prices
100
400
250
MAX()
↓
400
Used for
Highest salary
Highest marks
Most expensive product
Largest transaction
5. MIN()
Smallest value.
Prices
100
400
250
MIN()
↓
100
Used for
Cheapest product
Lowest salary
Minimum temperature
Earliest age
Aggregate Functions vs Normal Functions
Imagine
Students
| Name | Marks |
|---|---|
| Rahul | 90 |
| Priya | 80 |
| John | 70 |
A normal function works on one row at a time.
Example:
Rahul → 90
Priya → 80
John → 70
One input.
One output.
Aggregate function
Looks at
90
80
70
Together.
Returns
Average
↓
80
One result.
Easy way to remember:
Normal Function = Individual
Aggregate Function = Whole Team
Aggregate Functions + GROUP BY
Without GROUP BY
Sales
| Seller | Amount |
|---|---|
| Amit | 100 |
| Amit | 300 |
| Rahul | 200 |
SUM()
↓
600
Whole table.
With GROUP BY
Amit
↓
400
Rahul
↓
200
Now every seller gets their own total.
Real-Life Examples
School
Average marks of each class.
Use
GROUP BY Class
AVG()
Hospital
Patients per doctor.
GROUP BY Doctor
COUNT()
Restaurant
Revenue per waiter.
GROUP BY Waiter
SUM()
Bank
Largest transaction per customer.
GROUP BY Customer
MAX()
Uber
Average trip fare per driver.
GROUP BY Driver
AVG()
Common Beginner Mistakes
❌ Mistake 1
Thinking COUNT(column) always counts every row.
If a column contains NULL, those rows are not counted.
Example:
| Name | Phone |
|---|---|
| Rahul | 99999 |
| Priya | NULL |
| John | 88888 |
COUNT(*)= 3 (all rows)COUNT(Phone)= 2 (only non-NULL phone numbers)
❌ Mistake 2
Using SUM() on text columns.
You can only sum numeric values.
❌ Mistake 3
Thinking AVG() rounds automatically.
It can return decimal values.
Example:
80, 81
Average = 80.5
❌ Mistake 4
Expecting MAX() to return an entire row.
It returns only the maximum value from the specified column.
❌ Mistake 5
Forgetting GROUP BY when you want separate summaries for each category.
Without grouping, the aggregate function summarizes the entire table.
Real-World Business Examples
Netflix
Average watch time
Total subscribers
Amazon
Total revenue
Largest order
Average cart value
Total likes
Average comments
Highest viewed reel
Hospital
Number of patients
Average treatment cost
Bank
Total deposits
Largest withdrawal
Average account balance
Part 2 — Question & Answer (Progressive Learning)
Q1. What is an aggregate function?
Answer: An aggregate function performs a calculation on multiple rows and returns a single summarized result.
Q2. Why do we use aggregate functions?
Answer: To answer summary questions such as total sales, average salary, number of customers, highest marks, or lowest price.
Q3. Which five aggregate functions are used most often?
Answer: COUNT(), SUM(), AVG(), MAX(), and MIN().
Q4. What does COUNT() do?
Answer: It counts rows. COUNT(*) counts all rows, while COUNT(column) counts only non-NULL values in that column.
Q5. What does SUM() do?
Answer: It adds together all numeric values in a column and returns the total.
Q6. What does AVG() do?
Answer: It calculates the average of numeric values in a column.
Q7. What do MAX() and MIN() do?
Answer: MAX() returns the largest value, while MIN() returns the smallest value from a column.
Q8. How do aggregate functions work with GROUP BY?
Answer: GROUP BY divides rows into groups, and aggregate functions calculate a separate summary for each group.
Q9. Give a real-life example of aggregate functions.
Answer: An e-commerce company calculates total revenue using SUM(), counts orders using COUNT(), finds the average order value using AVG(), and identifies the highest and lowest order values using MAX() and MIN().
Q10. What is the difference between a normal function and an aggregate function?
Answer: A normal function processes one row at a time, while an aggregate function processes many rows together and returns one summarized value.
Part 3 — Top 5 MCQs
1. Which aggregate function calculates the total of numeric values?
A. COUNT()
B. SUM()
C. AVG()
D. MAX()
✅ Answer: B
2. Which function returns the highest value?
A. MIN()
B. AVG()
C. MAX()
D. COUNT()
✅ Answer: C
3. Which function calculates the average?
A. SUM()
B. COUNT()
C. AVG()
D. MAX()
✅ Answer: C
4. Which statement about COUNT(*) is correct?
A. It counts only non-NULL values.
B. It counts all rows.
C. It counts only numbers.
D. It returns the largest value.
✅ Answer: B
5. To calculate total sales for each city, which combination is most appropriate?
A. ORDER BY + MAX()
B. WHERE + MIN()
C. GROUP BY + SUM()
D. LIMIT + COUNT()
✅ Answer: C
🧠 Chapter 8 Summary
Aggregate functions summarize data across multiple rows.
The five essential aggregate functions are:
COUNT()→ Counts rows or non-NULLvalues.SUM()→ Adds numeric values.AVG()→ Calculates the average.MAX()→ Finds the largest value.MIN()→ Finds the smallest value.
Without
GROUP BY, an aggregate function summarizes the entire table.With
GROUP BY, it produces one summary per group.Aggregate functions are used daily in dashboards, analytics, reports, and business intelligence systems.
🎓 Mini Memory Trick
Imagine you're a class teacher:
👨🎓 COUNT() → "How many students are in my class?"
💰 SUM() → "How many total marks did the class score?"
📊 AVG() → "What's the class average?"
🏆 MAX() → "Who scored the highest?"
📉 MIN() → "What was the lowest score?"
If the principal asks about each class separately, you first use GROUP BY Class, then apply these aggregate functions.
🚀 Next Chapter
Chapter 9: Primary & Foreign Keys ⭐
You'll learn one of the most important concepts in relational databases:
Why every row needs a unique identity
What Primary Keys and Foreign Keys are
How tables are connected
Why relational databases are called "relational"
How databases maintain data integrity and prevent broken relationships.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.