Calculating Averages Using AVG() in MySQL

Course: MySQL Training

Topic: Calculating Averages Using AVG()

Course Link: MySQL Training Course

Demo Link: Register for a Free Course Demo

1. Introduction to AVG() in MySQL

The AVG() function is an aggregate function in MySQL used to calculate the arithmetic average of numeric values in a column or expression. It is useful when working with employee salaries, product prices, student marks, sales amounts, order values, customer ratings, and other numerical information.

Instead of retrieving every record and calculating the average manually, MySQL can perform the calculation directly using AVG(). The function normally returns a single average value for the selected rows. When combined with GROUP BY, it can calculate a separate average for each group.

For example, if a table contains product prices of 100, 200, 300, and 400, the average price is:

(100 + 200 + 300 + 400) / 4 = 250

MySQL can calculate this result with the following query:

SELECT AVG(price) AS average_price
FROM products;

The AVG() function ignores NULL values when calculating the average. If there are no qualifying non-NULL values, it returns NULL. Read the official MySQL aggregate function documentation.

2. Learning Objectives

After completing this topic, you will be able to:

• Understand the purpose of the MySQL AVG() function.
• Write the correct syntax for calculating averages.
• Calculate the average of a complete table or selected rows.
• Use AVG() with WHERE, GROUP BY, and HAVING.
• Calculate averages using distinct values.
• Understand how NULL values affect average calculations.
• Round and format average results for reports.
• Use averages with expressions, conditional logic, joins, and subqueries.
• Identify common mistakes and improve the accuracy of average calculations.

3. What Is an Average?

An average is a measure that represents the central value of a collection of numbers. The arithmetic mean is calculated by adding the included values and dividing the total by the number of included values.

3.1 Average Formula

Average = Sum of Included Values / Number of Included Values

Suppose five students receive the following marks: 60, 70, 80, 90, and 100.

The average is 400 divided by 5, which equals 80. MySQL can calculate this automatically using AVG(marks).

4. Syntax of the AVG() Function

The basic syntax is:

SELECT AVG(expression) AS alias_name
FROM table_name;

Explanation of the syntax:

The optional DISTINCT keyword can be used inside AVG() to calculate the average of unique values only.

AVG(expression)
AVG(DISTINCT expression)

5. Creating a Sample Database Table

To practise the AVG() function, create an employee table containing employee names, departments, salaries, and performance scores.

5.1 Create the Database

CREATE DATABASE avg_practice;

USE avg_practice;

5.2 Create the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    performance_score DECIMAL(5,2)
);

The salary column stores salary values with two decimal places. The performance_score column stores employee performance scores. Both columns support numeric calculations.

5.3 Insert Sample Records

INSERT INTO employees
(employee_id, employee_name, department, salary, performance_score)
VALUES
(1, 'Amit', 'IT', 45000.00, 85.00),
(2, 'Priya', 'HR', 40000.00, 90.00),
(3, 'Rahul', 'IT', 55000.00, 88.00),
(4, 'Sneha', 'Finance', 50000.00, 92.00),
(5, 'Karan', 'HR', 35000.00, 78.00),
(6, 'Neha', 'Finance', 60000.00, 95.00),
(7, 'Vikas', 'IT', 48000.00, 80.00),
(8, 'Pooja', 'HR', 42000.00, 86.00),
(9, 'Arjun', 'Finance', 52000.00, NULL),
(10, 'Meera', 'IT', NULL, 89.00);

5.4 View the Sample Data

SELECT *
FROM employees;

Review the records before calculating averages. The sample data contains a NULL salary and a NULL performance score so that you can observe how MySQL handles missing values.

6. Calculating the Average of a Column

The most common use of AVG() is calculating the average of all non-NULL values in a numeric column.

6.1 Calculate the Average Salary

SELECT AVG(salary) AS average_salary
FROM employees;

This query calculates the average salary of employees whose salary is not NULL. The missing salary is not treated as zero; it is excluded from both the sum and the count used for the average.

For the sample data, the included salaries total 427,000 across nine employees, giving an average salary of approximately 47,444.44.

6.2 Calculate the Average Performance Score

SELECT AVG(performance_score) AS average_performance
FROM employees;

The query calculates the average of the nine non-NULL performance scores. It excludes the missing score for Arjun.

6.3 Calculate Multiple Averages in One Query

SELECT
    AVG(salary) AS average_salary,
    AVG(performance_score) AS average_performance
FROM employees;

This query returns two calculated values in one row. It is useful for dashboards and summary reports that need multiple averages from the same dataset.

7. Using AVG() with WHERE

The WHERE clause filters rows before the aggregate function calculates the average. This lets you calculate an average for a particular condition.

7.1 Average Salary of IT Employees

SELECT AVG(salary) AS average_it_salary
FROM employees
WHERE department = 'IT';

Only employees in the IT department are considered. Their available salaries are 45,000, 55,000, and 48,000. The average is approximately 49,333.33.

7.2 Average Salary Above a Threshold

SELECT AVG(salary) AS average_high_salary
FROM employees
WHERE salary > 45000;

Only records with a salary greater than 45,000 are included. The average is calculated from the qualifying non-NULL salary values.

7.3 Average Performance Above a Threshold

SELECT AVG(performance_score) AS average_high_performance
FROM employees
WHERE performance_score >= 85;

This query calculates the average score of employees whose performance score is at least 85. Rows with missing performance scores do not satisfy this comparison.

7.4 Using Multiple Conditions

SELECT AVG(salary) AS average_it_salary
FROM employees
WHERE department = 'IT'
  AND salary >= 45000;

The query includes only IT employees whose salary is at least 45,000. The average is calculated after both conditions have been applied.

8. Using AVG() with GROUP BY

The GROUP BY clause divides records into groups. When combined with AVG(), MySQL calculates a separate average for each group.

8.1 Average Salary by Department

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department;

This query returns one row for each department. The expected results for the sample data are:

8.2 Average Performance by Department

SELECT
    department,
    AVG(performance_score) AS average_performance
FROM employees
GROUP BY department;

This query compares department performance. Each department's average is based on the available, non-NULL scores within that department.

8.3 Group by Multiple Columns

You can group records by more than one column when you need more detailed summaries.

SELECT
    department,
    employee_name,
    AVG(salary) AS employee_average_salary
FROM employees
GROUP BY department, employee_name;

Because each employee name and department combination identifies an individual in this sample, this query returns the available salary for each employee as that employee's average. In a real report, grouping by individual employees is useful only when that level of detail is required.

8.4 Group by a Date or Period

For a sales table that includes a date column, you can group transactions by month to calculate monthly average order values.

SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS order_month,
    AVG(order_amount) AS average_order_amount
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY order_month;

This example assumes an orders table containing order_date and numeric order_amount columns. The result contains one average for each month represented in the data.

9. Using AVG() with HAVING

The HAVING clause filters groups after aggregation. Use it when you want to show only groups whose calculated average meets a condition.

9.1 Departments with Average Salaries Above 45,000

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 45000;

This query returns departments whose average salary exceeds 45,000. Based on the sample data, Finance and IT qualify.

9.2 Departments with Average Performance of at Least 85

SELECT
    department,
    AVG(performance_score) AS average_performance
FROM employees
GROUP BY department
HAVING AVG(performance_score) >= 85;

MySQL calculates each department's average first, then retains only the groups that meet the condition.

9.3 WHERE vs HAVING

10. Using AVG(DISTINCT) to Calculate Unique-Value Averages

By default, AVG() includes every non-NULL value, including duplicates. The DISTINCT keyword changes the calculation so that each distinct value is counted only once.

10.1 Example Using Duplicate Values

Consider the following values:

10, 10, 20, 30

With the normal AVG() function, the calculation is:

(10 + 10 + 20 + 30) / 4 = 17.5

With AVG(DISTINCT ...), duplicate values are included only once:

(10 + 20 + 30) / 3 = 20

10.2 SQL Example

SELECT AVG(DISTINCT salary) AS average_unique_salary
FROM employees;

This query averages the distinct salary values rather than the salaries of all employees. Use this only when the business requirement specifically calls for averaging unique values. It is not a replacement for the ordinary employee salary average.

11. Understanding NULL Values in AVG()

MySQL aggregate functions such as AVG() ignore NULL values. A NULL represents missing or unknown information; it is not the same as zero.

11.1 Example of NULL Handling

Suppose the values in a column are 10, 20, and NULL. MySQL calculates the average using only 10 and 20:

SELECT AVG(score) AS average_score
FROM student_scores;

The result is 15, assuming the table contains those three records. MySQL does not divide the total of 30 by all three rows.

11.2 Compare AVG(), COUNT(), and COUNT(*)

SELECT
    AVG(salary) AS average_salary,
    COUNT(salary) AS non_null_salary_count,
    COUNT(*) AS total_employee_count
FROM employees;

COUNT(salary) counts rows where salary is not NULL. COUNT(*) counts all rows, including those with NULL salary. Comparing these values helps you understand how many records contribute to an average.

11.3 Treat Missing Values as Zero

If the business rule explicitly states that missing values should be treated as zero, you can use IFNULL() inside AVG().

SELECT AVG(IFNULL(salary, 0)) AS average_salary_including_missing_as_zero
FROM employees;

This query treats a missing salary as zero and includes that row in the denominator. The result will generally be lower than the average of available salaries. Do not use this method automatically; missing information may mean “unknown” rather than “zero.”

11.4 Average of an Empty Result

SELECT AVG(salary) AS average_salary
FROM employees
WHERE department = 'Marketing';

If no rows match the condition, AVG() returns NULL. The same applies when all matching values in the expression are NULL. You can use COALESCE() if the application requires a fallback display value.

SELECT COALESCE(AVG(salary), 0) AS average_salary
FROM employees
WHERE department = 'Marketing';

This displays zero when the average result is NULL. Keep in mind that the displayed zero means no average was available, not necessarily that the actual average salary is zero.

12. Rounding and Formatting Average Results

Average calculations can return decimal values. Use rounding or formatting functions when preparing reports.

12.1 ROUND()

SELECT ROUND(AVG(salary), 2) AS average_salary
FROM employees;

ROUND(value, 2) rounds the result to two decimal places. This is useful for salary, price, and financial reports.

12.2 ROUND() with GROUP BY

SELECT
    department,
    ROUND(AVG(salary), 2) AS average_salary
FROM employees
GROUP BY department;

This query displays each department's average salary rounded to two decimal places.

12.3 FORMAT()

SELECT FORMAT(AVG(salary), 2) AS formatted_average_salary
FROM employees;

FORMAT() returns a formatted string, commonly with two decimal places and thousands separators. It is helpful for presentation, but the result is a string rather than a numeric value. Keep numeric values numeric when further calculations or numeric sorting are required.

12.4 Difference Between ROUND() and FORMAT()

13. Calculating Averages Using Expressions

The expression inside AVG() does not have to be a single column. It can contain arithmetic operations that produce a numeric result for each row.

13.1 Average of a Discounted Price

SELECT
    AVG(price * 0.90) AS average_discounted_price
FROM products;

This example assumes the table contains a numeric price column. The expression calculates a 10% discount for each price before the average is calculated.

13.2 Average of Two Assessment Scores

SELECT
    student_id,
    (math_marks + science_marks) / 2 AS student_average
FROM student_results;

This query calculates an average for each student, not the overall average across students. If either score can be NULL, the expression will also return NULL for that row unless missing-value handling is added according to the intended rules.

13.3 Overall Average of a Row-Level Expression

SELECT
    AVG((math_marks + science_marks) / 2) AS overall_student_average
FROM student_results;

This query calculates the average of each row's two-subject average. It assumes both marks are available for the rows being considered. If NULL handling is required, define the rule explicitly instead of silently replacing missing scores.

14. Conditional Averages Using CASE

The CASE expression can be used inside AVG() to calculate an average for rows that meet a condition without excluding other groups from the query.

14.1 Average Salary for IT Employees

SELECT
    AVG(
        CASE
            WHEN department = 'IT' THEN salary
            ELSE NULL
        END
    ) AS average_it_salary
FROM employees;

The expression returns the salary for IT employees and NULL for other employees. Since AVG() ignores NULL, only IT salaries contribute to the average.

14.2 Calculate Multiple Conditional Averages

SELECT
    AVG(CASE WHEN department = 'IT'
        THEN salary ELSE NULL END) AS average_it_salary,
    AVG(CASE WHEN department = 'HR'
        THEN salary ELSE NULL END) AS average_hr_salary,
    AVG(CASE WHEN department = 'Finance'
        THEN salary ELSE NULL END) AS average_finance_salary
FROM employees;

This query returns multiple department averages in one row. It is useful for comparison dashboards where a single summary record is preferred.

15. Using AVG() with JOIN

Joins allow you to calculate averages using related tables. For example, an employee table can be joined to a department table to display department names alongside average salaries.

15.1 Example Tables

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employee_details (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10,2)
);

15.2 Average Salary by Department

SELECT
    d.department_name,
    AVG(e.salary) AS average_salary
FROM departments AS d
JOIN employee_details AS e
    ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

The query matches employees to their departments and calculates the average salary for each department.

15.3 Using LEFT JOIN

SELECT
    d.department_name,
    AVG(e.salary) AS average_salary
FROM departments AS d
LEFT JOIN employee_details AS e
    ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

A LEFT JOIN includes departments even when no employee matches. For a department without any non-NULL salary values, the average will be NULL. This can be useful for reports that must show every department.

15.4 Avoiding Duplicate Rows

Be careful when joining a table to another table that contains multiple matching records. A one-to-many join can repeat employee salary values and change the calculated average. Confirm the relationship between tables and aggregate at the appropriate level before joining additional details.

16. Using AVG() with Subqueries

A subquery is a query nested inside another SQL statement. Subqueries are useful when comparing a value or group average with an overall average.

16.1 Find Employees Earning Above the Overall Average

SELECT
    employee_name,
    department,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

The inner query calculates the average salary across the table. The outer query returns employees whose salary is greater than that average.

16.2 Compare Department Average with Overall Average

SELECT
    department,
    AVG(salary) AS department_average
FROM employees
GROUP BY department
HAVING AVG(salary) > (
    SELECT AVG(salary)
    FROM employees
);

This query displays departments whose average salary is higher than the overall employee average.

16.3 Average of Department Averages

SELECT AVG(department_average) AS average_of_department_averages
FROM (
    SELECT AVG(salary) AS department_average
    FROM employees
    GROUP BY department
) AS department_summary;

This calculates the average of the department-level averages. It is not necessarily equal to the average salary of all employees because each department average receives equal weight, regardless of the number of employees in each department.

17. AVG() and Window Functions

In MySQL versions that support window functions, AVG() can be used with an OVER() clause to calculate an average while keeping individual rows in the result. Unlike a simple aggregate query, a window function does not collapse all matching rows into one result.

17.1 Show Each Employee with the Overall Average Salary

SELECT
    employee_name,
    department,
    salary,
    AVG(salary) OVER () AS overall_average_salary
FROM employees;

Each employee row remains visible, and the overall average is repeated for each row. NULL salaries are ignored by the window average.

17.2 Show Each Employee with Their Department Average

SELECT
    employee_name,
    department,
    salary,
    AVG(salary) OVER (
        PARTITION BY department
    ) AS department_average_salary
FROM employees;

The PARTITION BY clause separates the data into departments for the window calculation. Each employee row displays the average salary of their department.

17.3 Difference Between GROUP BY and OVER()

18. AVG() Compared with SUM(), COUNT(), MIN(), and MAX()

MySQL provides several aggregate functions for summarizing numeric data. Each function answers a different question.

Example query:

SELECT
    COUNT(*) AS total_employees,
    COUNT(salary) AS employees_with_salary,
    SUM(salary) AS total_salary,
    AVG(salary) AS average_salary,
    MIN(salary) AS minimum_salary,
    MAX(salary) AS maximum_salary
FROM employees;

This query provides a useful overview of salary data in one result.

19. Common Mistakes When Using AVG()

19.1 Treating NULL as Zero Without a Business Rule

Incorrect assumption: A NULL salary automatically counts as zero.

Correct understanding: AVG() ignores NULL values. Use IFNULL() only if missing values should genuinely be treated as zero.

19.2 Using WHERE to Filter an Aggregate Result

Incorrect query:

SELECT department, AVG(salary) AS average_salary
FROM employees
WHERE AVG(salary) > 45000
GROUP BY department;

Correct query:

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 45000;

Use HAVING to filter groups based on aggregate calculations.

19.3 Averaging Duplicate Values Incorrectly

Using AVG(DISTINCT salary) changes the meaning of the calculation. If the goal is average salary per employee, use AVG(salary) so that each employee's available salary contributes to the result.

19.4 Confusing an Average of Averages with an Overall Average

An average of department averages gives each department equal weight. The overall average gives each included employee's salary equal weight. These results can differ when departments have different numbers of employees.

19.5 Using AVG() on Non-Numeric Data

Use AVG() with numeric values or expressions. Applying it to unsuitable data types can produce misleading results because MySQL may convert values to numbers. Store data using appropriate numeric types and validate incoming data.

19.6 Formatting a Number Too Early

FORMAT() produces a string. If the result will be used for further numeric calculations or sorting, prefer retaining the numeric result and formatting it in the presentation layer or using ROUND() where appropriate.

20. Best Practices for Using AVG()

• Choose a numeric data type appropriate for the information being stored.
• Understand whether NULL values mean unknown, unavailable, or zero.
• Use meaningful aliases such as average_salary and average_order_value.
• Use WHERE to filter individual rows before calculating the average.
• Use GROUP BY to calculate averages for categories such as departments, months, or products.
• Use HAVING to filter groups based on calculated averages.
• Use DISTINCT only when averaging unique values is required.
• Use ROUND() for numeric rounding and FORMAT() for display formatting.
• Check whether joins duplicate rows before calculating averages.
• Consider whether an average is an appropriate measure for the data; extreme values can distort it.
• Test queries with NULL values, empty result sets, and boundary conditions.
• For large tables, examine query execution plans and indexes on filtering or joining columns when performance matters.

21. Practical Project: Employee Salary Analysis

Project objective: Create a salary analysis report using the AVG() function in MySQL.

Project Requirements

1. Calculate the average salary of all employees.
2. Calculate the average salary for each department.
3. Display only departments whose average salary exceeds 45,000.
4. Find employees earning more than the overall average salary.
5. Calculate average salary and performance score in the same query.
6. Round department averages to two decimal places.
7. Compare each employee's salary with their department average using a window function.

Solution 1: Overall Average Salary

SELECT ROUND(AVG(salary), 2) AS average_salary
FROM employees;

Solution 2: Average Salary by Department

SELECT
    department,
    ROUND(AVG(salary), 2) AS average_salary
FROM employees
GROUP BY department
ORDER BY average_salary DESC;

Solution 3: Departments Above the Average Salary Threshold

SELECT
    department,
    ROUND(AVG(salary), 2) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 45000;

Solution 4: Employees Above the Overall Average

SELECT
    employee_name,
    department,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)
ORDER BY salary DESC;

Solution 5: Salary and Performance Summary

SELECT
    AVG(salary) AS average_salary,
    AVG(performance_score) AS average_performance
FROM employees;

Solution 6: Compare Employee Salary with Department Average

SELECT
    employee_name,
    department,
    salary,
    ROUND(
        AVG(salary) OVER (PARTITION BY department),
        2
    ) AS department_average_salary
FROM employees;

Expected learning outcome: You should be able to calculate overall averages, group averages, conditional averages, and comparisons with average values using practical SQL queries.

22. Practice Exercises

Use the sample employees table to solve the following questions.

1. Calculate the average salary of all employees.
2. Find the average performance score of all employees.
3. Calculate the average salary of HR employees.
4. Display average salary by department in descending order.
5. Display only departments with average salary above 40,000.
6. Calculate the average of distinct salary values.
7. Round the overall average salary to two decimal places.
8. Find employees whose salary is higher than the overall average.
9. Display total employee count, non-NULL salary count, and average salary together.
10. Display each employee alongside their department's average salary using a window function.

Practice Exercise Answers

Answer 1

SELECT AVG(salary) AS average_salary
FROM employees;

Answer 2

SELECT AVG(performance_score) AS average_performance
FROM employees;

Answer 3

SELECT AVG(salary) AS average_hr_salary
FROM employees
WHERE department = 'HR';

Answer 4

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY average_salary DESC;

Answer 5

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 40000;

Answer 6

SELECT AVG(DISTINCT salary) AS average_unique_salary
FROM employees;

Answer 7

SELECT ROUND(AVG(salary), 2) AS average_salary
FROM employees;

Answer 8

SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Answer 9

SELECT
    COUNT(*) AS total_employees,
    COUNT(salary) AS employees_with_salary,
    AVG(salary) AS average_salary
FROM employees;

Answer 10

SELECT
    employee_name,
    department,
    salary,
    AVG(salary) OVER (
        PARTITION BY department
    ) AS department_average_salary
FROM employees;

23. MySQL AVG() Interview Questions and Answers

Q1. What is the AVG() function in MySQL?

Answer: AVG() is an aggregate function that calculates the arithmetic average of non-NULL values in a numeric expression.

Q2. What is the syntax of AVG()?

Answer: The basic syntax is SELECT AVG(column_name) FROM table_name;.

Q3. Does AVG() include NULL values?

Answer: No. MySQL ignores NULL values in the average calculation.

Q4. What happens if all values are NULL?

Answer: The function returns NULL because there are no non-NULL values to average.

Q5. What is the difference between AVG() and AVG(DISTINCT)?

Answer: AVG() includes all non-NULL values, including duplicates. AVG(DISTINCT) includes each distinct non-NULL value only once.

Q6. Can AVG() be used with WHERE?

Answer: Yes. The WHERE clause filters rows before the average is calculated.

Q7. Can AVG() be used with GROUP BY?

Answer: Yes. GROUP BY allows MySQL to calculate a separate average for each group.

Q8. What is the role of HAVING with AVG()?

Answer: HAVING filters grouped results based on their calculated average, such as departments whose average salary is above a specified amount.

Q9. How can you round an average to two decimal places?

Answer: Use ROUND(AVG(column_name), 2).

Q10. What is the difference between COUNT(*) and COUNT(column_name) when analysing averages?

Answer: COUNT(*) counts all rows, while COUNT(column_name) counts only rows where that column is not NULL. This difference matters because AVG() ignores NULL expression values.

Q11. How do you find employees earning more than the average salary?

Answer: Use a subquery that calculates the overall average salary, then filter employees whose salary is greater than that result.

Q12. Can AVG() be used with a CASE expression?

Answer: Yes. A CASE expression can return values for selected rows and NULL for other rows, allowing conditional averages to be calculated.

Q13. What is the difference between GROUP BY and a window average?

Answer: GROUP BY produces grouped summary rows. AVG() OVER() calculates averages while preserving individual rows in the result.

Q14. Does AVG() always return an integer when the column is an integer?

Answer: No. Average results can contain fractional values. MySQL's result type depends on the input type; exact-value arguments such as integer and DECIMAL produce a DECIMAL result, while approximate-value arguments such as FLOAT and DOUBLE produce a DOUBLE result.

Q15. Is the average of department averages always equal to the overall average salary?

Answer: No. An unweighted average of department averages gives each department equal weight. The overall employee average accounts for the number of included salary values in each department.

24. Quick Revision Checklist

• AVG(column_name) calculates the average of non-NULL values.
• AVG(DISTINCT column_name) calculates the average of unique values.
• WHERE filters rows before aggregation.
• GROUP BY calculates averages for separate groups.
• HAVING filters groups after aggregation.
• ROUND(AVG(column_name), 2) rounds the result to two decimal places.
• IFNULL() can treat missing values as zero when that matches the business rule.
• COALESCE() can provide a fallback when AVG() returns NULL.
• Subqueries can compare individual values or group averages with an overall average.
• AVG() OVER() supports window calculations while preserving rows.
• Joins and duplicate rows can affect average results, so validate the query's row-level logic.

25. Conclusion

The MySQL AVG() function is an essential tool for analysing numerical data. It helps calculate average salaries, product prices, student marks, sales amounts, and performance scores without manually processing every record.

By combining AVG() with WHERE, GROUP BY, HAVING, DISTINCT, conditional expressions, joins, subqueries, and window functions, you can create useful reports and perform more advanced database analysis. Always consider NULL handling, duplicate rows, data types, and the business meaning of the average before interpreting the result.

Continue learning: Explore the MySQL Training Course to strengthen your database skills, and register for a course demo to learn more about the training.

Official reference: MySQL Aggregate Function Documentation