Blog

Mastering SQL AVG() Function

In SQL, the AVG() function is used to calculate the average value of a numeric column in a table. This tutorial will guide you through the basics of using the AVG() function in SQL, including syntax, examples, and scenarios.

Prerequisites:

• Basic understanding of SQL syntax.
• Familiarity with SQL SELECT statements.

Syntax of AVG() Function

SELECT AVG(column_name) FROM table_name;

• column_name: The name of the column for which you want to calculate the average.
• table_name: The name of the table containing the column.

Example Scenario: Calculating Average Salary
Suppose we have a table named employees with columns employee_id and salary. We want to calculate the average salary of all employees.

Sample SQL Code:

SELECT AVG(salary) AS average_salary FROM employees;

Explanation:

• We use the AVG() function to calculate the average salary from the salary column.
• The AS keyword is used to alias the result column as average_salary for better readability.

Handling NULL Values

If the column contains NULL values, the AVG() function ignores them in the calculation.

Example Scenario: Calculating Average Age:
Consider a table named students with columns student_id and age. Some students might not have their age recorded, leading to NULL values.

Sample SQL Code:

SELECT AVG(age) AS average_age FROM students;

Explanation:

• The AVG() function automatically excludes NULL values while calculating the average age.

Using AVG() with WHERE Clause

You can apply filters using the WHERE clause to calculate the average of specific rows.

Example Scenario: Calculating Average Salary for Senior Employees:
Assuming we have a designation column in the employees table, and we want to find the average salary of employees with the designation ‘Senior Engineer’.

Sample SQL Code:

SELECT AVG(salary) AS avg_salary_senior 
FROM employees 
WHERE designation = 'Senior Engineer';

Explanation:

• Here, we filter the rows using the WHERE clause to consider only those employees with the designation ‘Senior Engineer’ for calculating the average salary.

5. Grouping with AVG() and GROUP BY:
You can group the results based on certain criteria using the GROUP BY clause along with the AVG() function.

Example Scenario: Calculating Average Salary by Department:
Assuming the employees table has a department_id column, and we want to find the average salary for each department.

Sample SQL Code:

SELECT department_id, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department_id;

Explanation:

• We use the GROUP BY clause to group the results by department_id.
• Then, we calculate the average salary for each department using the AVG() function.

Conclusion:
In this tutorial, you’ve learned the basics of using the SQL AVG() function to calculate averages in various scenarios. Practice these examples to master the AVG() function in SQL and explore its applications in your database queries.