SQL Server AVG() Function

The AVG() function in SQL Server is a part of an aggregate function. We use this function to calculate the average value of an expression. This function ignores the NULL values. However, when the result set does not have any rows, it returns NULL. The AVG() is a built-in function usually used with the SELECT statement. This function accepts only one parameter that can be a column or valid expression and returns a single result to summarize the input data set. It can also work with the WHERE, GROUP BY, ORDER BY, and HAVING clauses to get the filtered result.

Syntax

The following syntax illustrate the AVG() function in SQL Server:

SELECT AVG(ALL | DISTINCT expression)  
FROM table_name(s)  
[WHERE conditions];

This function contains the following parameters:

Expression: It is the required expression representing the column name whose non-null values are used to calculate the average.

table_name(s): It is the name of a table from where we want to retrieve records. There can be specified more than one table.

WHERE conditions: It is an optional clause to define a condition that must be satisfied for selecting the records.

ALL|DISTINCT: ALL is used by default that instructs the function to operate on all values for calculation. In comparison, the DISTINCT tells the function to take only unique values.

AVG() Function Example

Let us understand how the AVG() function works in SQL Server with the help of various examples. We are going to take the 'employee_info' table to demonstrate the AVERAGE function practically that contains the following data:

Basic Example of AVERAGE Function

This example explains the basic use of an AVERAGE function in SQL Server. It returns the average value of an expression, including null, non-null, and duplicates. Suppose we want to calculate the average working hours of all employees listed in the table. We can write the query as below:

SELECT AVG(working_hours) AS "Average Working Hours" 
FROM employee_info; 

Executing the query will return the below output that shows the average working hours of employees:

AVERAGE Function with WHERE Clause

SQL Server uses the WHERE clause to filter the records of a table. This example will use the WHERE clause with the AVG() function to get the average working hours of employees whose occupation is a writer.

SELECT AVG(working_hours) AS "Average Working Hours" 
FROM employee_info
WHERE occupation = 'Writer'; 

When we execute the query, we see that the average working hours of the writer is 11 hours:

AVERAGE Function with GROUP BY Clause

SQL Server uses the GROUP BY clause to group rows having the same values into summary rows. We use this clause because it produces the summary reports to make further strategies. The below query will use the AVG() function to return the total working hours of employees in each occupation listed in the table:

SELECT occupation, AVG(working_hours) AS "Average Working Hours" 
FROM employee_info
GROUP BY occupation;  

Executing the query will return the average working hours of employees associated with each occupation:

If we try to combine aggregate and non-aggregated columns in a SELECT statement, we will get an error. Here is the error message:

AVERAGE Function with ORDER BY Clause

SQL Server uses the ORDER BY clause to sort the table either in ascending or descending order. The below example will use the AVG() function with the ORDER BY clause, calculate the average working hours of employees associated with each occupation and then sorts the result set in the descending order of the aggregated average value:

SELECT occupation, AVG(working_hours) AS "Average Working Hours" 
FROM employee_info
GROUP BY occupation
ORDER BY AVG(working_hours) DESC;

Executing the statement will display the result in the descending order based on the aggregated average value:

AVERAGE Function with HAVING Clause

SQL Server uses the HAVING clause to filter the grouped rows, and this clause always needs the GROUP BY clause to produce the output. The below example will use the HAVING clause with the AVG() function. It calculates the average working hours of employees in each occupation and then checks whether the aggregated average is greater than ten or not. If this is true, the corresponding result returns in the ascending order of an aggregated average value:

SELECT occupation, AVG(working_hours) AS "Average Working Hours" 
FROM employee_info
GROUP BY occupation
HAVING AVG(working_hours)>10
ORDER BY AVG(working_hours);

Executing the statement will return the average working hour of employees greater than ten in the occupation:

AVERAGE Function with DISTINCT Clause

SQL Server uses the DISTINCT keyword to remove the duplicate data from the table. Suppose we want to calculate the total working hours of unique employees in each occupation listed in the employee_info table. We can write the query to get these data as below:

SELECT occupation, AVG( DISTINCT working_hours) AS "Average Working Hours" 
FROM employee_info
GROUP BY occupation;

Executing the query will return the below result that shows working hours of unique employees in each occupation in the table:

Conclusion

This article will explain a complete overview of how and when to use the AVG() function in SQL Server. This function works with numeric data types only. Here we have discussed various examples based on several use cases of the AVERAGE function, such as how to get the average working hours of employees in the table using the WHERE, GROUP BY, ORDER BY, and HAVING clauses.

Next TopicSQL Server COUNT Function

← prev next →