SQL AVG

The AVG() function in SQL is used to calculate the average value of a numeric column. It returns the mean (average) value of the specified column.

 

Syntax of SQL AVG

SELECT AVG(column_name)
FROM table_name
WHERE condition;

• AVG(): The aggregate function used to calculate the average of values.
• column_name: The name of the column whose average you want to calculate.
• table_name: The name of the table containing the column.
• condition: Optional condition to filter the rows for which the average is calculated.

 

Key Points

• The AVG() function only works with numeric data types (e.g., INT, FLOAT, DECIMAL).
• It ignores NULL values. If the column has NULL values, they are not included in the average calculation.
• The result of AVG() is a decimal number, even if the column contains integer values.

 

Example of SQL AVG

Consider the following table:

Table: Employees

To calculate the average salary of all employees:

SELECT AVG(salary) AS average_salary
FROM employees;

Output:

Explanation:

• The average salary is calculated by summing all the values in the salary column and then dividing by the number of rows.
• The result is 61000 which is the average salary of the employees.

 

SQL AVG with Conditions

You can apply a condition to calculate the average for a subset of data. For example, to find the average salary of employees with a salary greater than 55,000:

SELECT AVG(salary) AS average_salary
FROM employees
WHERE salary > 55000;

Output:

Explanation:

• Only employees with a salary greater than 55,000 are considered.
• The average of 60000, 65000, and 70000 is 65000.

 

SQL AVG with Grouping

You can also use AVG() with the GROUP BY clause to calculate averages for different groups. For example, to find the average salary of employees in different departments (assuming you have a department column):

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

Output:

Explanation:

• The AVG() function calculates the average salary for each department separately, grouped by the department column.

 

Summary

The AVG() function in SQL is used to calculate the average value of a numeric column. It ignores NULL values and can be used with conditions or in combination with GROUP BY to calculate averages for different groups. The result is a decimal value, even if the column has integer data.