SQL

AVG() in SQL

📊 The AVG() function is an aggregate function in SQL that calculates the average (mean) value of a numeric column. It is commonly used for reporting, statistical analysis, performance evaluation, financial calculations, and business intelligence. The AVG() function works only with numeric data types and ignores NULL values during calculation.

📖 What is AVG()?

The AVG() function computes the arithmetic mean by adding all numeric values in a column and dividing the total by the number of non- NULL values. It returns a single numeric value representing the average.

Information

AVG() works with numeric data types such as INT, DECIMAL, FLOAT, and DOUBLE. It automatically ignores NULL values.

📝 Basic Syntax

AVG() Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;

📊 Sample Table

Consider the following Students table:

StudentID	Name	Department	Marks	AttendancePercentage
101	Alice	Computer Science	92	96
102	Bob	Mathematics	85	90
103	Charlie	Physics	78	88
104	David	Computer Science	95	98
105	Eva	Mathematics	88	92

1️⃣ Calculating the Average

Calculate the average marks of all students.

Average Marks

SELECT AVG(Marks) AS AverageMarks
FROM Students;

Result:

AverageMarks
87.6

2️⃣ AVG() with WHERE

Use the WHERE clause to calculate the average for selected rows only.

Average Marks of Computer Science Students

SELECT AVG(Marks) AS AverageCSMarks
FROM Students
WHERE Department = 'Computer Science';

3️⃣ AVG() with DISTINCT

Use the DISTINCT keyword to calculate the average of unique values only.

Average of Distinct Marks

SELECT AVG(DISTINCT Marks) AS UniqueAverage
FROM Students;

Tip

AVG(DISTINCT Column) removes duplicate values before calculating the average.

📊 AVG() with GROUP BY

The GROUP BY clause groups records, and AVG() calculates the average for each group.

Average Marks by Department

SELECT Department,
       AVG(Marks) AS AverageMarks
FROM Students
GROUP BY Department;

Sample Result:

Department	AverageMarks
Computer Science	93.5
Mathematics	86.5
Physics	78.0

📈 AVG() with HAVING

The HAVING clause filters grouped results after averages have been calculated.

Departments with Average Marks Above 85

SELECT Department,
       AVG(Marks) AS AverageMarks
FROM Students
GROUP BY Department
HAVING AVG(Marks) > 85;

🔗 AVG() with JOIN

The AVG() function is frequently combined with joins to calculate averages across related tables.

Average Course Score per Student

SELECT s.Name,
       AVG(c.Score) AS AverageScore
FROM Students s
JOIN Courses c
ON s.StudentID = c.StudentID
GROUP BY s.Name;

🧮 AVG() with Expressions

You can calculate the average of an expression instead of a single column.

Average Marks After Bonus

SELECT AVG(Marks + 5) AS AverageBonusMarks
FROM Students;

⚠️ AVG() and NULL Values

The AVG() function ignores NULL values when calculating the average. Only non- NULL values are included.

Column Values	AVG() Result
10, 20, NULL, 30	20

Important

If all selected values are NULL, the AVG() function returns NULL.

📊 Common Uses of AVG()

Purpose	Example
Average Marks	AVG(Marks)
Average Salary	AVG(Salary)
Average Sales	AVG(SalesAmount)
Average Attendance	AVG(AttendancePercentage)
Department-wise Average	AVG(...) GROUP BY Department

💼 Real-World Example

A university administrator wants to prepare a report showing the average marks of students in each department.

Department-wise Average Marks

SELECT Department,
       AVG(Marks) AS AverageMarks
FROM Students
GROUP BY Department
ORDER BY AverageMarks DESC;

This query generates a report displaying the average marks for each department, sorted from the highest average to the lowest.

⚠️ Best Practices

Best Practice

Use AVG() only with numeric columns, combine it with WHERE to calculate filtered averages, use GROUP BY for grouped summaries, and remember that AVG() ignores NULL values. Use COALESCE() if you want to replace a NULL result with a default value such as 0.

🚀 Key Points to Remember

📌 AVG() calculates the arithmetic mean of numeric values.
📌 It ignores NULL values.
📌 AVG(DISTINCT Column) calculates the average of unique values only.
📌 It is commonly used with WHERE, GROUP BY, and HAVING.
📌 AVG() can be used with expressions and joins.
📌 It is widely used in analytics, reporting, and business intelligence.

>>"The AVG() function helps transform raw numbers into meaningful insights by revealing the typical value within a dataset."

Summary

✅ The AVG() function is one of the most important SQL aggregate functions for calculating average values. Whether analyzing marks, salaries, sales, or attendance, it provides valuable statistical insights that support reporting, analytics, and informed decision-making.