SQL Notes
Learn the SQL AVG() function in detail, understand how averages are calculated, work with GROUP BY, handle NULL values, and generate meaningful business insights.
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.
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 = 56250
Output:
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:
56250
The 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 = 47500
Output:
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 Category
AVG() 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 = 60000
Output:
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.67
Using DISTINCT:
SELECT AVG(DISTINCT Bonus)
FROM Employees;Calculation:
5000 + 10000 ------------ 15000 ÷ 2 = 7500
Duplicates 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 = 450
Output:
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 = 1375
Output:
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 Records
performance 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 = 0
Reality:
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 Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, functions, avg, function
Related SQL Complete Guide Topics