MySQL Aggregate Functions | COUNT(), SUM(), AVG(), MIN() and MAX()

In many situations, users need summarized information instead of individual records. For example, a school may want to know the total number of students, the highest marks, the average marks, or the total fees collected. MySQL provides Aggregate Functions to perform such calculations quickly and accurately.

Aggregate functions work on multiple rows of a table and return a single result. These functions are widely used in reports, dashboards, business analytics, banking, healthcare, and educational institutions.

---

Learning Outcomes

After studying this chapter, you will be able to:

• Understand the purpose of Aggregate Functions.
• Use the COUNT() function to count records.
• Calculate totals using the SUM() function.
• Find averages using the AVG() function.
• Determine the highest and lowest values using MAX() and MIN().
• Apply Aggregate Functions in real-life SQL queries.

---

What are Aggregate Functions?

---

Sample Student Table

Roll_No	Name	Class	Marks	Fee
101	Aarav	XI	91	45000
102	Diya	XI	84	45000
103	Kabir	XII	95	48000
104	Riya	XII	78	48000
105	Vivaan	XI	88	45000

---

1. COUNT() Function

Syntax

SELECT COUNT(column_name)
FROM table_name;

Example

SELECT COUNT(*)
FROM Student;

The above query returns the total number of records present in the Student table.

---

Counting Students of Class XI

SELECT COUNT(*)
FROM Student
WHERE Class = 'XI';

---

2. SUM() Function

Syntax

SELECT SUM(column_name)
FROM table_name;

Example

SELECT SUM(Fee)
FROM Student;

The above query calculates the total fee amount of all students.

---

3. AVG() Function

Syntax

SELECT AVG(column_name)
FROM table_name;

Example

SELECT AVG(Marks)
FROM Student;

The query returns the average marks of all students.

---

Comparison of COUNT(), SUM() and AVG()

Function	Purpose	Returns
COUNT()	Counts records	Number of rows
SUM()	Adds numeric values	Total
AVG()	Calculates average	Mean value

---

---

4. MIN() Function

Syntax

SELECT MIN(column_name)
FROM table_name;

Example

SELECT MIN(Marks)
FROM Student;

The above query returns the lowest marks scored by a student.

---

5. MAX() Function

Syntax

SELECT MAX(column_name)
FROM table_name;

Example

SELECT MAX(Marks)
FROM Student;

The above query returns the highest marks scored by a student.

---

Comparison of All Aggregate Functions

Function	Purpose	Example	Result
COUNT()	Counts records	COUNT(*)	Total number of rows
SUM()	Calculates total	SUM(Fee)	Total fee collected
AVG()	Calculates average	AVG(Marks)	Average marks
MIN()	Finds the smallest value	MIN(Marks)	Lowest marks
MAX()	Finds the largest value	MAX(Marks)	Highest marks

---

Practical SQL Queries

Example 1: Count Total Students

SELECT COUNT(*)
FROM Student;

---

Example 2: Count Students of Class XII

SELECT COUNT(*)
FROM Student
WHERE Class = 'XII';

---

Example 3: Calculate Total Fee

SELECT SUM(Fee)
FROM Student;

---

Example 4: Calculate Average Marks

SELECT AVG(Marks)
FROM Student;

---

Example 5: Find Highest Marks

SELECT MAX(Marks)
FROM Student;

---

Example 6: Find Lowest Marks

SELECT MIN(Marks)
FROM Student;

---

Solved Example 1

Display the total number of students in the Student table.

Solution:

SELECT COUNT(*)
FROM Student;

---

Solved Example 2

Calculate the total fee collected from all students.

Solution:

SELECT SUM(Fee)
FROM Student;

---

Solved Example 3

Find the average marks of all students.

Solution:

SELECT AVG(Marks)
FROM Student;

---

Solved Example 4

Display the highest marks obtained by any student.

Solution:

SELECT MAX(Marks)
FROM Student;

---

Solved Example 5

Display the lowest marks obtained by any student.

Solution:

SELECT MIN(Marks)
FROM Student;

---

Real-Life Applications

Organization	Requirement	Aggregate Function
School	Total number of students	COUNT()
School	Average marks of a class	AVG()
Bank	Total account balance	SUM()
Hospital	Highest patient bill	MAX()
Library	Lowest book price	MIN()
Retail Store	Total sales amount	SUM()

---

Common Errors

Mistake	Correct Practice
Using SUM() on text columns.	Use SUM() only with numeric data.
Using AVG() on character data.	AVG() works only on numeric values.
Confusing COUNT(*) with COUNT(column).	COUNT(*) counts all rows, whereas COUNT(column) counts only non-NULL values in that column.
Using aggregate functions without understanding the data type.	Ensure the selected column supports the function.
Expecting multiple rows as output.	Aggregate functions return a single summarized value.

---

Interview Corner

---

---

Competency-Based Questions

1. A school wants to know the total number of students studying in Class XI. Write the SQL query using an appropriate aggregate function.
2. Write an SQL query to calculate the average marks of all students in the Student table.
3. A school accountant wants to calculate the total fees collected from all students. Which aggregate function should be used? Write the SQL query.
4. Differentiate between the MIN() and MAX() functions with suitable examples.
5. Explain the difference between COUNT(*) and COUNT(column_name).

---

Multiple Choice Questions

1. Aggregate functions return:
   • (a) Multiple rows
   • (b) A single summarized value
   • (c) Only text values
   • (d) Only NULL values
2. Which function is used to count the total number of records?
   • (a) SUM()
   • (b) AVG()
   • (c) COUNT()
   • (d) MAX()
3. Which function calculates the total of numeric values?
   • (a) COUNT()
   • (b) SUM()
   • (c) AVG()
   • (d) MIN()
4. Which function returns the average value of a numeric column?
   • (a) MAX()
   • (b) COUNT()
   • (c) AVG()
   • (d) SUM()
5. Which function returns the smallest value in a column?
   • (a) MAX()
   • (b) MIN()
   • (c) COUNT()
   • (d) SUM()
6. Which function returns the highest value in a column?
   • (a) AVG()
   • (b) SUM()
   • (c) MAX()
   • (d) COUNT()
7. Which SQL query returns the average marks of students?
   • (a) SELECT SUM(Marks) FROM Student;
   • (b) SELECT AVG(Marks) FROM Student;
   • (c) SELECT COUNT(Marks) FROM Student;
   • (d) SELECT MAX(Marks) FROM Student;
8. Which function should be used to calculate the total fee collected?
   • (a) COUNT()
   • (b) AVG()
   • (c) SUM()
   • (d) MIN()
9. Which function ignores NULL values while counting a specific column?
   • (a) COUNT(column_name)
   • (b) COUNT(*)
   • (c) SUM()
   • (d) MAX()
10. Which of the following is not an aggregate function?
    • (a) COUNT()
    • (b) AVG()
    • (c) MAX()
    • (d) ORDER BY

---

Quick Revision

Function	Purpose
COUNT()	Counts the number of records.
SUM()	Calculates the total of numeric values.
AVG()	Calculates the average value.
MIN()	Returns the smallest value.
MAX()	Returns the largest value.

---

Important Points to Remember

• Aggregate functions perform calculations on multiple rows and return a single value.
• COUNT(*) counts all rows in a table.
• COUNT(column_name) counts only the non-NULL values in the specified column.
• SUM() and AVG() should be used only with numeric columns.
• MIN() returns the smallest value, while MAX() returns the largest value.
• Aggregate functions can be combined with the WHERE clause to calculate results for selected records.
• Aggregate functions are frequently used for generating reports, summaries, and statistical analysis.
• These functions are essential for analyzing large volumes of data efficiently.

---

CBSE Exam Tips

• Remember the purpose of each aggregate function and its correct syntax.
• Practice writing SQL queries using COUNT(), SUM(), AVG(), MIN(), and MAX().
• Do not confuse COUNT(*) with COUNT(column_name).
• Use SUM() and AVG() only with numeric columns.
• Read the question carefully to identify whether it asks for a total, count, average, highest, or lowest value.
• Expect competency-based questions that require selecting the appropriate aggregate function for a real-life situation.

---

Summary

MySQL Aggregate Functions are used to perform calculations on groups of records and return a single summarized result. COUNT() counts records, SUM() calculates totals, AVG() computes averages, MIN() finds the smallest value, and MAX() finds the largest value. These functions are widely used in schools, businesses, hospitals, banks, and many other organizations to generate reports and analyze data efficiently. A strong understanding of aggregate functions is essential for solving SQL problems and performing well in the CBSE Class 11 Informatics Practices examination.

---

What's Next?

In the next chapter, you will learn GROUP BY Clause in MySQL, where aggregate functions are applied to different groups of records instead of the entire table. You will also understand how to generate class-wise, department-wise, or category-wise summaries using SQL.