When working with databases, we often need to find the average value of a particular column. Whether it’s calculating the average salary of employees, the average order value, or even the average rating of a product, SQL provides a built-in function to make our job easier. That function is AVG(). So, let’s dive into how AVG works in SQL and explore the best AVG examples to understand it in action.
What is the AVG() Function in SQL?
The AVG() function in SQL returns the average value of a given column. It performs an arithmetic mean calculation, summing up all the values in a column and then dividing by the total number of non-null records. This makes it an essential tool when working with statistical data.
The syntax for AVG() is straightforward:
SELECT AVG(column_name) FROM table_name;
Now, let’s explore its functionality in different scenarios.
Basic Example of AVG()
Let’s say we have a table named employees with the following data:
| id | name | salary |
|---|---|---|
| 1 | Alice | 50000 |
| 2 | Bob | 55000 |
| 3 | Charlie | 60000 |
If we want to calculate the average salary, we can use this SQL query:
SELECT AVG(salary) AS avg_salary FROM employees;
This would return:
+------------+
| avg_salary |
+------------+
| 55000.0000 |
+------------+
Handling NULL Values in AVG()
One important thing to keep in mind is that AVG() ignores NULL values. Let’s modify our employees table by adding a new record with a NULL salary:
| id | name | salary |
|---|---|---|
| 1 | Alice | 50000 |
| 2 | Bob | 55000 |
| 3 | Charlie | 60000 |
| 4 | David | NULL |
Even though we have a NULL salary in the table, the AVG() function will completely ignore it. The calculation will still be:
(50000 + 55000 + 60000) / 3 = 55000
Using AVG() with GROUP BY
If you want to calculate the average salary per department, you can combine AVG() with GROUP BY. Assume our table now has a department column:
| id | name | salary | department |
|---|---|---|---|
| 1 | Alice | 50000 | HR |
| 2 | Bob | 55000 | IT |
| 3 | Charlie | 60000 | IT |
| 4 | David | 70000 | HR |
If we run the following query:
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
The result will be:
+------------+------------+
| department | avg_salary |
+------------+------------+
| HR | 60000.0000 |
| IT | 57500.0000 |
+------------+------------+
Using AVG() with ROUND()
The AVG function can return long decimal values. If you want to round the output, you can use the ROUND() function:
SELECT ROUND(AVG(salary), 2) AS avg_salary FROM employees;
This will return the average salary rounded to two decimal places.
AVG() with DISTINCT Values
If we want to calculate the average of distinct values (ignoring duplicates), we can use DISTINCT inside AVG:
SELECT AVG(DISTINCT salary) FROM employees;
This will calculate the average considering only unique salary values.
Common Mistakes When Using AVG()
There are a few common mistakes developers make when using the AVG() function:
- Forgetting that
NULLvalues are ignored, which might lead to incorrect assumptions. - Not using
ROUND()when needed, which can result in unintended decimal precision. - Using
AVG()on non-numeric columns, which will result in an error. - Expecting
AVG()to consider all records, including NULLs.
Conclusion
The AVG() function in SQL is a powerful tool when dealing with numerical data. Whether you’re calculating simple averages or working with grouped data, it can help in generating valuable insights from a dataset. Understanding how it handles NULL values, rounding, and duplicate values is key to using it effectively in real-world applications.
Other interesting article:
How SUM works in SQL? Best SUM examples