DBMS Aggregate Functions with Examples

 

Aggregate Functions in DBMS:

 

·         Aggregate functions are functions that take a collection (a set or multi set) of values as input and return a single value.

·         A group function returns a result based on a group of rows.

·         Following are the built in aggregate functions:

1.    Average – avg()

2.    Minimum – min()

3.    Maximum – max()

4.    Total – sum()

5.    Count – count()

 

 

1.    avg():

 

It returns the average value of the specified column of number data type.

 

Syntax: avg( column_name)

 

 Example: to find the average salary of all employees:

 

select avg(salary) from emp;

 

 

2.    sum():

 

This function returns the summation of the specified column of number data type.

 

Syntax: sum( column_name)

 

Example: to find the sum of the salaries of all the employees:

 

select sum( salary)  from emp;

 

3.    min():

 

It returns the lowest value from the specified column of number data type.

 

Syntax: min( column_name)

 

Example:  to find the minimum salary from all the employees:

 

select min( salary)  from emp;

4.    max():

 

This function returns the highest value of the specified column of number data type.

 

Syntax: max( column_name)

 

Example:  to find maximum salary from all the employees:

 

select max( salary)  from emp;

 

 

5.    count():

 

The count function is used to count the number of rows.

 

a)     count(*):

 

It counts all rows, inclusive of duplicate and nulls.

 

Example: to count all rows in ‘emp’ table:

 

select count(*) from emp;

 

b)    count( column_ name):

 

It is used to count the number of values present in in the specified column without including null values.

 

Example: to count the rows having Commission amount in ‘emp’ table:

 

select count(commission) from emp;

 

c)     count(distinct column_ name):

 

This function is used to eliminate duplicate and null values in the specified column.

 

 Example: to count the number of rows having distinct departments from ‘emp’ table:

 

select count( distinct deptno) from emp;