AVG function in SQL Server

This article will show you in detail how to use the AVG () numerical processing function in SQL Server with specific syntax and examples to better visualize and capture functions.

Describe

The AVG function in SQL Server returns the average value of an expression or average value according to the specified column of the selected row. The values ​​NULL omitted do not count.

Syntax

To calculate the average value, we use the AVG function in SQL Server syntax as follows:

 SELECT AVG(cot) 
 FROM bang 
 [WHERE dieukien]; 

Parameters :

1. cot: column or calculated value, the expression you want to average
2. state: the table used to retrieve the record. Must have at least 1 table in the FROM clause.
3. dieukien: optional. Conditions that the record must meet to be selected.

Note :

1. AVG function can be used in later versions of SQL Server: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005.

For example

Take a look and explore some examples of AVG functions in SQL Server.

Suppose, we have the following data table:

Example 1: Calculate the average number of posts of the website Quantrimang

 SELECT AVG(Sobai) AS "So bai trung binh" 
 FROM Quantrimang 
 WHERE Sobai > 100; 
 
 Result: So bai trung binh 128.2 

In this example we calculate the average value for all the values ​​in the data to be calculated, so the average result is calculated as follows, taking the sum of the Sobai column then dividing by the total number of lines with other data. value greater than 100.

Example 2: Use DISTINCT

 SELECT AVG(DISTINCT Sobai) AS "So bai trung binh" 
 FROM Quantrimang 
 WHERE Sobai > 100; 
 
 Result: So bai trung binh 135 

This example uses the keyword DISTINCT , so the repeated values ​​are counted only once. In the given data table, the value '101' appears twice, but is counted only once, so the total number of calculated lines will be 4, the average result is calculated as follows:

 (152 + 101 + 122 + 165)/4 = 135 

Example 3: Calculate the average value according to the specified column of the selected row

To calculate the average value according to the specified column of the selected rows, we use the GROUP BY clause.

The following example calculates the average of all records related to a large category and you will do the following:

 SELECT Chuyenmuclon, AVG(Sobai) AS "So bai trung binh" 
 FROM Quantrimang 
 GROUP BY Chuyenmuclon; 
 
 Result: 
 Chuyenmuclon So bai trung binh 
 Laptrinh 108 
 Mang xa hoi 158.5 
 Trinh duyet web 94 

Previous article: ABS function in SQL Server

Next lesson: CEILING function in SQL Server