SQL Server AVG Function

AVG function calculate average of data.A set of data can be average by AVG ,average can be done with group.


We have created a table and corresponding data to do some sample example.

CREATE TABLE student

(

       id INT NOT NULL,

       name VARCHAR (500) NOT NULL,

       age INT NOT NULL,

       adress VARCHAR (500) NOT NULL,

       date_of_birth DATETIME NOT NULL,

       height DECIMAL(10,2) NOT NULL

)

INSERT INTO student(id,name,age,adress,date_of_birth,height)

SELECT 1,'John1',12,'34,student street','01/08/2018',10.5

UNION

SELECT 1,'John2',13,'35,student street','02/08/2018',11

UNION

SELECT 1,'John3',12,'36,student street','03/08/2018',11.5

UNION

SELECT 1,'John4',13,'37,student street','04/08/2018',12

UNION

SELECT 1,'John5',12,'38,student street','05/08/2018',12.5

UNION

SELECT 1,'John6',13,'39,student street','06/08/2018',13.5

UNION

SELECT 1,'John7',15,'40,student street','07/08/2018',14

UNION

SELECT 1,'John8',14,'41,student street','08/08/2018',14.5

UNION

SELECT 1,'John9',12,'42,student street','09/08/2018',15

UNION

SELECT 1,'John10',15,'43,student street','10/08/2018',15.5

UNION

SELECT 1,'John11',16,'44,student street','11/08/2018',16

AVG work on numeric , decimal, integer values but does not work string of text value  or date value. You can apply distinct in average function. Average function work with group by and partition by also.

Here is example of simple AVG. 
 
Example 1

SELECT avg(age)

FROM student

Result : 13

Here is example of simple AVG with distinct value. 

Example 2

SELECT avg(DISTINCT age)

FROM student

Result : 14


Here is example of AVG cannot be used with string data type. 
Example 3

SELECT avg(name)

FROM student

Result : Operand data type varchar is invalid for avg operator.

Here is example of AVG cannot be used with date data type. 
Example 4 

SELECT avg(date_of_birth)

FROM student

Result : Operand data type datetime is invalid for avg operator.

Here is example of AVG with group by . 
Example 4 

         SELECT age as age_group,avg(height) AS number_of_count

         from student

         GROUP BY age

age_group number_of_count

12        12.375000

13        12.166666

14        14.500000

15        14.750000

16        16.000000

Here is example of AVG with Partition by . 
Example 5
 

SELECT DISTINCT age as age_group,

avg(height) OVER (PARTITION BY age) AS height

FROM student

age_group number_of_count

12        12.375000

13        12.166666

14        14.500000

15        14.750000

16        16.000000