SQL AVG Function | Average Value of Expression

SQL AVG is an inbuilt function in SQL Server. It returns the average of a particular field/column in the rows matching criteria. Also, it can return the average of all the fields of all rows in the table if you don’t specify any criteria. In this article, we will discuss the SQL AVG Function. Also, we will discuss a few examples of using it.

Note: Average is the total sum of values divided by the number of rows.

             Sum of Values
Average = -------------------
           Number of Values

Syntax

SELECT AVG(column_name) FROM table_name WHERE condition;

  • column_name: The column to average.
  • table_name: The table from which to average.
  • condition: The condition to specify which rows to average. The function will return the average of fields of only those rows which match the WHERE condition.

Demo Table

For instance, consider the demo table to refer to all the queries in this article.

mysql> SELECT * FROM employees;
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  1 | David  |  20000 |          5 | Alpha |
|  2 | Monica |  25000 |          6 | Beta  |
|  3 | John   |  15000 |          3 | Beta  |
|  4 | Mary   |  18000 |          4 | Gamma |
|  5 | Ross   |  30000 |          8 | Alpha |
+----+--------+--------+------------+-------+

Examples

Let’s discuss a few examples of using the AVG function in SQL.

Example 1: Average of Fields of All Rows in Table

For instance, we want to find the average of a field in the rows in the table. We can use the SQL AVG function without specifying any where condition. In this case, the function returns the average of all salaries in the table.

mysql> SELECT AVG(salary) FROM employees;
+-------------+
| AVG(salary) |
+-------------+
|  21800.0000 |
+-------------+

Example 2: Average of a Field in Rows Satisfying a Criteria

Similarly, you can specify a WHERE condition in the query. The AVG Function in SQL will average a column of only those rows which satisfy the criteria. For instance, let’s find the average of the salaries of employees in the Alpha Team.

mysql> SELECT AVG(salary) FROM employees WHERE team = 'Alpha';
+-------------+
| AVG(salary) |
+-------------+
|  23000.0000 |
+-------------+

Conclusion

In conclusion, we discussed the SQL AVG Function in this article. You can read more about it on the Official Microsoft Documentation. Additionally, you can also learn more about SQL on Concatly.

Spread the Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *