Average – SQL Server AVG function

AVG function (Average) in SQL Server (Transact-SQL) – AVG function returns the arithmetic mean for all found records. Arithmetic mean of a group of numbers is their sum divided by their number.

For example, we have a table with users that stores their ages. With AVG we can find their average age.

AVG function syntax in SQL Server (Transact-SQL):

SELECT AVG(aggregate_expr)
FROM tabs
[WHERE conds];

Or AVG function syntax when results are grouped in one or more columns:

SELECT expression1, expression2, … expression_n,
AVG(aggregate_expr)
FROM tabs
[WHERE conds]
GROUP BY expr1, expr2, … expr_n;

Parameters or arguments:

• expr1,2,… – expressions that were not included in the AVG function and must be entered into the GROUP BY operator at the end of the SQL sentence.
• aggregate_expr – expression or column for averaging.
• tabs – the tables from which we need to get the records. For the FROM operator, you need at least one table.
• WHERE conds are optional. Conditions that must be met for those records that we have selected.

Application

AVG function can be used in the following versions of SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

One Field Example
We will look at various examples of the AVG SQL Server function to understand how to properly use the AVG function in SQL Server (Transact-SQL).

For example, you can learn how the average number of all products where the quantity is greater than 0:

SELECT AVG(quantity_id) AS "Average Quantity_id"
FROM prods
WHERE quantity_id > 0;

In this example of AVG function, we have specified the alias as “Average Quantity” to the expression AVG(quantity). As a result, “Average Quantity” will be displayed as a field name when returning the resulting set.

Let’s find the average salary across the table:

SELECT AVG(salary_id) as avg FROM workers

The result of SQL query execution:

Example of using DISTINCT

You can use a DISTINCT operator with AVG function. For example, the following SQL operator returns an average salary with unique values where the salary exceeds $30,000 per year.

SELECT AVG_id(DISTINCT salary_id) AS "Average Salary_id"
FROM employees
WHERE salary_id > 30000;

If there were two salaries of $40,000 per year, only one of these values would be used in AVG functions.

Example of formula use

The expression contained in the AVG function does not have to be a single field. You can also use a formula. For example, you may need the average commission.

SELECT AVG(order_value_id * 0.10) AS "Average Commission_id"
FROM order_details_id;

Example of using GROUP BY

You can also use the AVG function to return the department name and average number (in the corresponding department). For example,

SELECT department_id, AVG(quantity_id) AS "Average Quantity_id"
FROM prods
GROUP BY depart;

Since you have specified one column in the SELECT operator that is not included in the AVG function, you must use the GROUP BY offer. Therefore, the field must be specified in the operator GROUP BY.

Fundamentals of SQL using Average Function