SQL Topics
AVG Function
title: AVG Function
In business, education, finance, healthcare, and many other fields, averages help us understand overall performance and trends.
For example:
- What is the average salary of employees?
- What is the average product price?
- What is the average student score?
- What is the average order value?
- What is the average monthly revenue?
Looking at individual records often makes it difficult to understand the bigger picture. Averages simplify large datasets into a single meaningful value.
SQL provides the AVG() Function for this purpose.
The AVG() function calculates the arithmetic mean of numeric values in a column. It is one of the most widely used aggregate functions in reporting, analytics, business intelligence, and performance measurement systems.
Understanding AVG() is essential because averages are used in almost every data-driven decision-making process.
What is the AVG() Function?
The AVG() function calculates the average value of a numeric column.
Example:
SELECT AVG(Salary)
FROM Employees;Output:
56250Instead of displaying every salary individually, SQL calculates the average salary.
Why is AVG() Important?
Suppose a company has:
500 EmployeesManagement wants to know:
Average Salary
Average Bonus
Average Performance Score
Average Project CostViewing all individual values would not provide a quick understanding.
AVG() helps summarize data efficiently.
Without AVG():
Retrieve all values
↓
Add values manually
↓
Divide by total records
↓
Time-consumingWith AVG():
SELECT AVG(Salary)
FROM Employees;SQL performs everything automatically.
Basic AVG() Syntax
The general syntax is:
SELECT AVG(column_name)
FROM table_name;Example:
SELECT AVG(Marks)
FROM Students;This returns the average marks of all students.
Understanding the Syntax
Example:
SELECT AVG(Salary)
FROM Employees;SELECT
Specifies the operation.
AVG()
Calculates the average value.
Salary
Numeric column used for calculation.
FROM
Specifies the source table.
Employees
Table containing the data.
How AVG() Works Internally
Consider the following salaries:
| Salary |
|---|
| ---------- |
| 50000 |
| 60000 |
| 45000 |
| 70000 |
Query:
SELECT AVG(Salary)
FROM Employees;Internal calculation:
50000
+
60000
+
45000
+
70000
=
225000
225000 ÷ 4
=
56250Output:
56250SQL performs the calculation automatically.
Creating a Sample Table
CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(100),
Department VARCHAR(50),
Salary DECIMAL(10,2)
);Insert data:
INSERT INTO Employees VALUES
(1, 'Rahul', 'IT', 50000),
(2, 'Priya', 'HR', 60000),
(3, 'Amit', 'IT', 45000),
(4, 'Neha', 'Finance', 70000);Calculating Average Salary
Query:
SELECT AVG(Salary)
FROM Employees;Result:
56250The company's average salary is ₹56,250.
AVG() with WHERE
Often, businesses need averages for specific records.
Example:
SELECT AVG(Salary)
FROM Employees
WHERE Department = 'IT';Process:
Filter IT Employees
↓
Calculate Average Salary
↓
Return ResultCalculation:
50000 + 45000
-----------
95000
95000 ÷ 2
=
47500Output:
47500AVG() with Multiple Conditions
Example:
SELECT AVG(Salary)
FROM Employees
WHERE Department = 'IT'
AND Salary > 40000;Only matching records participate in the calculation.
AVG() with GROUP BY
One of the most common uses of AVG() is grouping data.
Example:
SELECT Department,
AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;Result:
| Department | AverageSalary |
|---|---|
| IT | 47500 |
| HR | 60000 |
| Finance | 70000 |
Each department gets its own average salary.
Why GROUP BY + AVG() is Important
Organizations frequently need reports such as:
Average Sales by Region
Average Salary by Department
Average Score by Class
Average Revenue by Product CategoryAVG() and GROUP BY make these reports possible.
AVG() with HAVING
HAVING filters grouped results.
Example:
SELECT Department,
AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000;Result:
| Department | AverageSalary |
|---|---|
| HR | 60000 |
| Finance | 70000 |
Only departments with an average salary greater than ₹50,000 are displayed.
AVG() and NULL Values
One of the most important concepts in SQL is NULL handling.
Consider:
| Salary |
|---|
| ---------- |
| 50000 |
| NULL |
| 70000 |
Query:
SELECT AVG(Salary)
FROM Employees;Calculation:
50000
+
70000
=
120000
120000 ÷ 2
=
60000Output:
60000Notice:
NULL is ignored.AVG() does not include NULL values in either the sum or the count.
AVG(DISTINCT)
Sometimes duplicate values should not influence the average.
Example:
| Bonus |
|---|
| -------- |
| 5000 |
| 5000 |
| 10000 |
Normal AVG():
SELECT AVG(Bonus)
FROM Employees;Calculation:
5000 + 5000 + 10000
-------------------
20000 ÷ 3
=
6666.67Using DISTINCT:
SELECT AVG(DISTINCT Bonus)
FROM Employees;Calculation:
5000 + 10000
------------
15000 ÷ 2
=
7500Duplicates are removed before calculating the average.
AVG() with Expressions
AVG() can calculate averages of expressions.
Example:
SELECT AVG(Price * Quantity)
FROM OrderDetails;Suppose:
| Price | Quantity |
|---|---|
| 100 | 5 |
| 200 | 2 |
Calculation:
100 × 5 = 500
200 × 2 = 400
(500 + 400) ÷ 2
=
450Output:
450Real-World Example
Imagine an online shopping platform.
Orders table:
| OrderID | Amount |
|---|---|
| 1 | 500 |
| 2 | 1500 |
| 3 | 2500 |
| 4 | 1000 |
Query:
SELECT AVG(Amount)
FROM Orders;Calculation:
500 + 1500 + 2500 + 1000
------------------------
5500 ÷ 4
=
1375Output:
1375Management instantly knows the average order value.
Business Reporting Example
Average monthly sales:
SELECT
MONTH(OrderDate) AS SalesMonth,
AVG(Amount) AS AverageOrderValue
FROM Orders
GROUP BY MONTH(OrderDate);Result:
| SalesMonth | AverageOrderValue |
|---|---|
| January | 1200 |
| February | 1500 |
| March | 1800 |
Such reports are widely used in business analytics.
Performance Considerations
AVG() is generally efficient.
For large datasets:
Millions of Recordsperformance depends on:
- Indexes
- Query optimization
- Storage engine
- Database architecture
Filtering data before calculating averages often improves performance.
Common Errors
Using AVG() on Text Columns
Wrong:
SELECT AVG(Name)
FROM Employees;AVG() works only with numeric data.
Forgetting GROUP BY
Wrong:
SELECT Department,
AVG(Salary)
FROM Employees;Most databases require:
GROUP BY Department;Assuming NULL Equals Zero
Wrong assumption:
NULL = 0Reality:
NULL is ignored.Misinterpreting AVG(DISTINCT)
AVG(DISTINCT) removes duplicate values before calculation.
It does not simply average all rows.
Best Practices
Use Aliases
Good:
SELECT AVG(Salary) AS AverageSalary
FROM Employees;Improves readability.
Filter Unnecessary Records
Use WHERE before AVG whenever possible.
Use GROUP BY for Reports
Produces meaningful summaries.
Understand NULL Behavior
Remember:
AVG() ignores NULL values.Verify Data Types
AVG() should only be used on numeric columns.
Common Interview Questions
What does AVG() do?
AVG() calculates the arithmetic mean of numeric values.
Does AVG() include NULL values?
No.
NULL values are ignored.
Can AVG() be used with GROUP BY?
Yes.
It is commonly used to calculate averages for groups.
What is AVG(DISTINCT)?
It calculates the average using only unique values.
Summary
The AVG() function is one of the most important SQL aggregate functions. It helps developers and analysts calculate averages quickly and efficiently, making it essential for reporting, analytics, performance evaluation, and business intelligence.
In this lesson, you learned:
- What AVG() is
- Why AVG() is important
- Basic syntax
- Internal working
- AVG() with WHERE
- AVG() with GROUP BY
- AVG() with HAVING
- AVG(DISTINCT)
- NULL handling
- Real-world examples
- Performance considerations
- Best practices
Mastering AVG() is essential because averages are one of the most commonly used metrics in modern database systems.
Next Step
Continue to the next lesson:
MIN() and MAX() Functions →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for AVG Function.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, functions, avg
Related SQL Topics