AGGREGATE FUNCTIONS IN SQL SERVER

Dept Table

Aggregate Functions

Are the built in functions , that operate  on group of values and they are used to calculate aggregates like

Sum

Average

Max

Min

Count

Aggregate functions are classified into

1 convenient aggregate functions

2 statitisal aggregate function

Convenient aggregate function

Sum(<colname>)

Returns the sum of all values in the given column

Syntax

Sum(expr)

Select sum(Sal) from emp

Avg(<colname>)

Returns average of all values in the given column

Syntax

Avg(expr)

Select avg(sal) from emp

Max(<colname>)

Returns the max value of the given column

Min(<colname>)

Returns the min value of the given column

Find total and average salary paid to employees

Select sum(Sal),avg(sal) from emp

Find the highest and lowest  salaries paid to employee

Select max(sal) as highestsalary, min(sal) as lowestsalary from emp

Display max salary of 20 th dept

Select max(sal) from emp where deptno=20

Select max(ename) from emp

Based on ascii

Select max(hiredate) from emp

Find the total salary paid to employee

Select sum(sal) from emp

Display total salary paid to manager

Select sum(sal) from emp where job=’manager’

Count(<colname>)

Returns the count of non null values in the given columns.

Return type of count function is int

Count(*)

Returns total no of rows in the table

 Count_big(*)

It includes null+data

It is big int

Find no of employees working in the company and no of employees drawing commission

Select count(*) , count(comm) from emp

Statistical  aggregate functions

Var(<colname>)

Returns varience for all the values in the given columns

Varp(<colname>)

Returns varience  for population of all values in the given column

Stdev(<colname>)

Returns standard deviation for all the values in the given column.