SQL Aggregate Functions

Aggregate functions perform calculations on multiple rows of a table and return a single result. They are commonly used to summarize data, generate reports, and perform statistical analysis.

According to the CBSE Class 12 Informatics Practices syllabus, students should know the following aggregate functions:

• MAX()
• MIN()
• AVG()
• SUM()
• COUNT()
• COUNT(*)

---

Sample Table : Student

RollNo	Name	Marks	City
101	Amit	85	Jaipur
102	Neha	92	Delhi
103	Rohan	78	Jaipur
104	Priya	95	Mumbai
105	Karan	81	Delhi

---

What are Aggregate Functions?

---

1. MAX() Function

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

Syntax

MAX(column_name)

Example

SELECT MAX(Marks)
FROM Student;

Output

95

---

Another Example

SELECT MAX(Name)
FROM Student;

Returns the last name alphabetically.

---

2. MIN() Function

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

Syntax

MIN(column_name)

Example

SELECT MIN(Marks)
FROM Student;

Output

78

---

3. AVG() Function

The AVG() function calculates the average of numeric values.

Syntax

AVG(column_name)

Example

SELECT AVG(Marks)
FROM Student;

Output

86.2

---

4. SUM() Function

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

Syntax

SUM(column_name)

Example

SELECT SUM(Marks)
FROM Student;

Output

431

---

5. COUNT() Function

The COUNT() function counts the number of non-NULL values in a column.

Syntax

COUNT(column_name)

Example

SELECT COUNT(Marks)
FROM Student;

Output

5

---

6. COUNT(*)

The COUNT(*) function counts all rows in the table, including rows containing NULL values.

Syntax

COUNT(*)

Example

SELECT COUNT(*)
FROM Student;

Output

5

---

Difference Between COUNT(column) and COUNT(*)

COUNT(column)	COUNT(*)
Counts only non-NULL values.	Counts all rows.
Ignores NULL values.	Includes rows even if some columns contain NULL values.
Applied on a specific column.	Applied to the entire table.

---

Using Aggregate Functions Together

SELECT
MAX(Marks),
MIN(Marks),
AVG(Marks),
SUM(Marks),
COUNT(*)
FROM Student;

---

Using Aggregate Functions with WHERE

SELECT AVG(Marks)
FROM Student
WHERE City='Delhi';

---

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

---

Summary of Aggregate Functions

Function	Purpose	Example
MAX()	Largest value	MAX(Marks)
MIN()	Smallest value	MIN(Marks)
AVG()	Average value	AVG(Marks)
SUM()	Total value	SUM(Marks)
COUNT()	Counts non-NULL values	COUNT(Marks)
COUNT(*)	Counts all rows	COUNT(*)

---

Real-Life Applications

Function	Application
MAX()	Highest salary or highest marks.
MIN()	Lowest temperature or lowest marks.
AVG()	Average attendance or average sales.
SUM()	Total revenue or total marks.
COUNT()	Number of students or employees.

---

Common Errors

Error	Reason
Using AVG() on text data	AVG() works only on numeric columns.
Incorrect column name	Unknown column error.
Syntax Error	Missing brackets or commas.

---

Quick Revision

Function	Returns
MAX()	Largest value
MIN()	Smallest value
AVG()	Average value
SUM()	Total
COUNT()	Number of non-NULL values
COUNT(*)	Total rows

---

CBSE Exam Tips

• Aggregate functions return a single value for multiple rows.
• AVG() and SUM() work only with numeric data.
• MAX() and MIN() can be used with both numeric and text columns.
• Remember the difference between COUNT(column) and COUNT(*).
• Practice combining aggregate functions with the WHERE clause.

---

Summary

SQL Aggregate Functions summarize data by performing calculations on multiple rows. Functions such as MAX(), MIN(), AVG(), SUM(), COUNT(), and COUNT(*) are widely used to generate reports and analyze data. These functions are an important part of the CBSE Class 12 Informatics Practices syllabus and are frequently used in both theory and practical examinations.