Previous
Written by: rJTutorial Team | Last Updated:

SQL AVG

The AVG() function is one of the most frequently used aggregate functions in SQL. It is designed to calculate the arithmetic mean of a numeric column, helping developers and data analysts quickly extract insights from large datasets such as finding the average order value, the mean temperature over a month, or typical employee compensation.

Developer Tip: In real-world applications, you will rarely use AVG() alone. You will almost always combine it with the ROUND() function to limit the number of decimal places in your final report or UI.

 

Syntax of SQL AVG

To use the AVG function, you include it in your SELECT statement. Here is the standard syntax:

SELECT AVG(column_name)
FROM table_name
WHERE condition;
  • AVG(column_name): This tells the database to sum all values in the column and divide by the count of non-null rows.
  • column_name: This must be a numeric column (integers, floats, decimals).
  • table_name: The source table you are querying.
  • condition: Use this to filter data before the average is calculated (e.g., averaging sales only for the year 2023).
Best Practice: Always use the AS keyword to give your result a descriptive name (an alias). Without it, the database might return a generic column name like AVG(salary) or expression1, which is harder to work with in your application code.

 

Key Points

  • Data Types: The function is strictly for numeric data. Attempting to use it on strings or dates will usually result in an error or a result of zero, depending on your database engine.
  • NULL Handling: This is a critical point AVG() automatically ignores NULL values. It does not treat them as zero; it simply excludes those rows from both the sum and the count.
  • Result Format: Even if your column contains whole integers, the result of an AVG() calculation is typically returned as a decimal (float or numeric) to ensure precision.
Watch Out: If all rows in your result set are NULL, or if the table is empty, AVG() will return NULL, not 0. You can use the COALESCE() function if you need to guarantee a zero is returned instead.

 

Example of SQL AVG

Let's look at a practical scenario. Imagine we are managing a small team and want to find the "market rate" for our current staff.

Table: Employees

employee_id name salary
1 John 50000
2 Alice 60000
3 Bob 55000
4 Sarah 65000
5 David 70000

To calculate the average salary of all employees, we run this query:

SELECT AVG(salary) AS average_salary
FROM employees;

Output:

average_salary
60000

Explanation:

  • The database adds all salaries: 50000 + 60000 + 55000 + 65000 + 70000 = 300,000.
  • It then divides by the number of employees (5).
  • The final result is 60,000.
Common Mistake: Beginners often try to calculate an average manually using SUM(column) / COUNT(*). While this seems correct, COUNT(*) includes NULLs, while SUM() and AVG() ignore them. This can lead to mathematically incorrect results if your data contains NULL values.

 

SQL AVG with Conditions

In many cases, you don't want the average of the entire table. You might want to exclude outliers or focus on a specific demographic. For example, if you want to find the average salary of only your senior-level earners (those making more than 55,000):

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

Output:

average_high_salary
65000

Explanation:

  • The WHERE clause filters the rows first. Only Alice (60k), Sarah (65k), and David (70k) are included.
  • The average is calculated as: (60000 + 65000 + 70000) / 3 = 65000.

 

SQL AVG with Grouping

The most powerful way to use AVG() is with the GROUP BY clause. This allows you to generate reports that compare different categories side-by-side. For instance, you might want to see the average salary for each department in your company.

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

Output:

department average_salary
HR 52500
IT 68000
Sales 61000

Explanation:

  • The database engine separates the rows into "buckets" based on their department.
  • The AVG() function is then run independently for each bucket.
  • This is the standard way to create summary tables for dashboards and business reports.

 

Summary

The AVG() function is an essential tool for data analysis in SQL. It streamlines the process of calculating means while handling numeric precision and skipping over empty (NULL) data points. By mastering AVG() alongside WHERE and GROUP BY, you can turn raw data into meaningful business intelligence with just a few lines of code.

Previous