SQL Server – Avg()

Define:

             Avg() is an Aggregate function which is use to get average of the numbers of columns.

Syntax:

AVG([All | Distinct]numeric_expression)

All:

             By default AVG(average) function applies to all.

Distinct:

             Specifies that AVG(average) is performed on each unique instance of a value, regardless how many times value had occurred.

Numeric_Expression:

              It is a numeric expression on which average to be performed. It could be column with numeric values or any unit which can be measured.

Return Types:

             Int, BigInt, Decimal, Money, Float.                                                                       

Example Table:

Employee Table:

empid       name                                salary

----------- ------------------------ --------

1001        Allyn                                10000 

1002        Bailey                               14000 

1003        Becky                              12000 

1004        Calista                              12000 

1005        Elisa                                 14000 

1006        Franklyn                           12000 

1007        Hervey                             14000 

1008        Jackson                            15000 

1009        Kimberly                          18000 

1010        Lecia                                16000 

(10 row(s) affected)

Example: All

             Suppose we have table employee, which have following data and we need average of salary of all employees.

     SELECT AVG(SALARY) AS 'Average Salary' FROM EMP;

    

Result:

Average Salary

-----------------

13700.000000

(1 row(s) affected)

Example: Distinct

             If we need average salary for all employee who have distinct salary.

             SELECT AVG(DISTINCT SALARY) AS 'Average Salary' FROM EMP;

Result:

Average Salary

-----------------

14166.666666

(1 row(s) affected)