SQL Aggregate Functions (CBSE Class 12 Computer Science - 083)

When working with databases, we often need summary information instead of individual records. For example, we may want to know the highest marks, lowest marks, average marks, total marks, or the number of students. SQL provides Aggregate Functions to perform these calculations efficiently.

Learning Objectives

• Understand Aggregate Functions.
• Use MAX() to find the largest value.
• Use MIN() to find the smallest value.
• Use AVG() to calculate the average.
• Use SUM() to calculate the total.
• Use COUNT() to count records.

---

Sample Student Table

StudentID	Name	Gender	Class	Section	City	Marks	DeptID
101	Riya	F	XII	A	Jaipur	95	1
102	Aman	M	XII	B	Delhi	91	2
103	Priya	F	XII	A	Jaipur	88	1
104	Rahul	M	XII	C	Mumbai	76	3
105	Neha	F	XII	B	Delhi	84	2
106	Arjun	M	XII	A	Jaipur	92	1
107	Simran	F	XII	C	Mumbai	81	3
108	Karan	M	XII	B	Delhi	69	2

---

What are Aggregate Functions?

Aggregate functions perform calculations on multiple rows and return a single result.

---

Common Aggregate Functions

Function	Purpose
MAX()	Returns the highest value.
MIN()	Returns the lowest value.
AVG()	Returns the average value.
SUM()	Returns the total of numeric values.
COUNT()	Returns the number of records.

---

MAX() Function

The MAX() function returns the highest value from a column.

Syntax

SELECT MAX(column_name)
FROM table_name;

Example

SELECT MAX(Marks)
FROM Student;

Output

95

---

Using Alias with MAX()

SELECT MAX(Marks) AS HighestMarks
FROM Student;

---

MIN() Function

The MIN() function returns the smallest value from a column.

Syntax

SELECT MIN(column_name)
FROM table_name;

Example

SELECT MIN(Marks)
FROM Student;

Output

69

---

AVG() Function

The AVG() function calculates the average value of a numeric column.

Syntax

SELECT AVG(column_name)
FROM table_name;

Example

SELECT AVG(Marks)
FROM Student;

Output

84.5

---

SUM() Function

The SUM() function calculates the total of all numeric values in a column.

Syntax

SELECT SUM(column_name)
FROM table_name;

Example

SELECT SUM(Marks)
FROM Student;

Output

676

---

COUNT() Function

The COUNT() function counts the number of records or non-NULL values.

---

COUNT(*)

COUNT(*) counts all rows in the table.

Example

SELECT COUNT(*)
FROM Student;

Output

8

---

COUNT(Column_Name)

COUNT(column_name) counts only the non-NULL values in the specified column.

Example

SELECT COUNT(Marks)
FROM Student;

Since every student has marks, the output will also be 8.

---

COUNT(DISTINCT Column_Name)

COUNT(DISTINCT column_name) counts only the unique values in a column.

Example

SELECT COUNT(DISTINCT City)
FROM Student;

Output

3

The three unique cities are Jaipur, Delhi, and Mumbai.

---

Using Aggregate Functions with WHERE

Highest Marks in Jaipur

SELECT MAX(Marks)
FROM Student
WHERE City='Jaipur';

---

Average Marks of Female Students

SELECT AVG(Marks)
FROM Student
WHERE Gender='F';

---

Total Marks of Students in Section A

SELECT SUM(Marks)
FROM Student
WHERE Section='A';

---

Number of Students from Delhi

SELECT COUNT(*)
FROM Student
WHERE City='Delhi';

---

Summary Table of Aggregate Functions

Function	Returns	Example
MAX()	Largest value	MAX(Marks)
MIN()	Smallest value	MIN(Marks)
AVG()	Average value	AVG(Marks)
SUM()	Total value	SUM(Marks)
COUNT()	Number of records	COUNT(*)

---

Execution Flow

SELECT
   │
   ▼
WHERE (Optional)
   │
   ▼
Aggregate Function
   │
   ▼
Single Result

---

Important Notes

• Aggregate functions return a single value.
• AVG() and SUM() work only on numeric columns.
• COUNT(*) counts all rows.
• COUNT(column_name) ignores NULL values.
• Aggregate functions can be combined with the WHERE clause.

---

Common Errors

• Using SUM() on text columns.
• Confusing COUNT(*) with COUNT(column_name).
• Using aggregate functions without understanding the effect of NULL values.
• Expecting aggregate functions to return multiple rows.
• Using AVG() on character data.

---

Exam Tips

---

Frequently Asked Questions (FAQs)

1. What is an aggregate function?

An aggregate function performs calculations on multiple rows and returns a single result.

2. Which function returns the highest value?

The MAX() function.

3. Which function returns the average of numeric values?

The AVG() function.

4. What is the difference between COUNT(*) and COUNT(column_name)?

COUNT(*) counts all rows, whereas COUNT(column_name) counts only non-NULL values in that column.

5. Can aggregate functions be used with the WHERE clause?

Yes. The WHERE clause filters records before the aggregate function performs its calculation.

---

Summary

• Aggregate functions summarize data by returning a single result.
• MAX() returns the largest value.
• MIN() returns the smallest value.
• AVG() calculates the average.
• SUM() calculates the total.
• COUNT() counts records or non-NULL values.
• Aggregate functions are commonly used with the WHERE clause to analyze filtered data.