Group Functions in SQL

These functions take a group of rows into consideration and returns a single value as an output.

Count (*)

It returns the no. of rows that can be retrieved by the given 'select' statement.

Example

select Count (*) from Emp where depno = 30

select Count (*) from Emp where Job = 'Clerk'

Count (colname)

It returns the no. of Not Null values that are present in the specified column.

Example

select Count (comm) from Emp

select Count (M62) from Emp

NOTE

All the Group functions will not take Null values into consideration.

Sum (colname)

It returns the sum of the specified column.

Example

select Sum (sal) from Emp

select Sum (comm) from Emp

Avg (colname)

It returns the average of the specified column i.e., Sum(colname) / Count(colname)

Example

select Avg (sal) from Emp

select Avg (comm) from Emp

Max (colname)

It returns the highest value of the specified column.

Example

select Max (sal) from Emp

Min (colname)

It returns the least value of the specified column. 

Example

select Min (sal) from Emp

Stdev (colname)

It returns the standard deviation of the specified column.

Example

select Stdev (sal) from Emp

Var (colname)

It returns the variance of the specified column.

Example

select Var (sal) from Emp

Distinct function

It returns the values of the specified column by eliminating duplicates.

Example

select Distinct job from Emp