MySQL AVG() Function

Introduction

In this chapter, we will learn about the AVG() function in MySQL. The AVG() function is an aggregate function that returns the average value of a numeric column. It is widely used to calculate the mean of a dataset, providing insights into the typical value within a set of numbers. We will cover the syntax for the AVG() function, examples of its usage, and important considerations for using it in MySQL.

Syntax

The basic syntax for using the AVG() function in MySQL is:

SELECT AVG(column_name)
FROM table_name
[WHERE condition];

Example: Calculate the Average Salary

SELECT AVG(salary) AS average_salary
FROM employees;

Full Example

Let’s go through a complete example where we create a database and table, insert data, and demonstrate the usage of the AVG() function.

1. Create a Database and Table

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('Rahul', 'Sharma', 'Sales', 50000.00),
('Priya', 'Singh', 'Marketing', 60000.00),
('Amit', 'Kumar', 'Sales', 55000.00),
('Neha', 'Verma', 'IT', 58000.00),
('Sahil', 'Mehta', 'IT', 62000.00),
('Kiran', 'Patel', 'Sales', 53000.00);

2. Using AVG()

Example: Calculate the Average Salary

SELECT AVG(salary) AS average_salary
FROM employees;

Output

Example: Calculate the Average Salary by Department

You can use the AVG() function with the GROUP BY clause to calculate the average salary for each department.

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

Output

Example: Calculate the Average Salary for a Specific Department

You can use the AVG() function with the WHERE clause to calculate the average salary for employees in a specific department.

SELECT AVG(salary) AS sales_average_salary
FROM employees
WHERE department = 'Sales';

Output

Using AVG() with Other Aggregate Functions

The AVG() function can be used in combination with other aggregate functions for more complex queries.

Example: Calculate the Average and Total Salary by Department

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

Output

Important Considerations

• NULL Values: The AVG() function ignores NULL values. Ensure that the column being averaged does not have NULL values if this is not the desired behavior.
• Data Types: The AVG() function works with numeric data types. Ensure that the column being averaged contains numeric values.
• Performance: The AVG() function can impact performance, especially on large datasets. Consider indexing columns used in the WHERE clause to improve performance.

Conclusion

The AVG() function in MySQL is used for calculating the average value of a numeric column. This chapter covered the syntax for using the AVG() function, provided examples of its usage, and discussed important considerations.