MySQL AVG() Function

 The AVG() function in MySQL is used to calculate the average value of a numeric column in a database. It returns the mean value of the specified column, considering all the rows in the table (or a subset if a WHERE clause is applied).

Syntax:

SELECT AVG(column_name) FROM table_name;

• column_name: The column for which you want to find the average.
• table_name: The name of the table where the column is located.

Example Usage:

1. Basic Example

If you have a table named employees with a column salary, and you want to find the average salary of all employees, you would write:

SELECT AVG(salary) AS average_salary
FROM employees;

This will return the average salary of all employees in the employees table. The result is aliased as average_salary.

2. With WHERE Clause

You can use the WHERE clause to find the average for a specific subset of data. For example, if you want to find the average salary of employees in the "Sales" department:

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

This will calculate the average salary only for employees in the "Sales" department.

3. With Grouping

You can also use AVG() with the GROUP BY clause to calculate the average for different groups. For example, if you want to find the average salary by department:

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

This will return the average salary for each department in the company.

Important Notes:

• NULL values: The AVG() function ignores NULL values. Only non-NULL values are considered when calculating the average.
• Data Type: The result of AVG() is returned as a decimal value, even if the column is of integer type. This ensures precision in calculations.

Example with Data:

Let's assume you have the following employees table:

id	name	salary	department
1	Alice	5000	HR
2	Bob	6000	Sales
3	Charlie	7000	HR
4	David	8000	Sales
5	Eve	9000	IT

If you want to find the average salary of all employees:

SELECT AVG(salary) AS average_salary
FROM employees;

This would return:

average_salary
7000.00

If you want the average salary by department:

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

This would return:

department	average_salary
HR	6000.00
Sales	7000.00
IT	9000.00

Conclusion:

The AVG() function is a powerful tool for quickly calculating the average of numeric values within a database. It works across all rows in a table or any subset defined by a WHERE clause and can be used in conjunction with GROUP BY for more detailed analysis.