Relational Query Languages |AGGREGATE FUNCTIONS OR OPERATIONS

AGGREGATE FUNCTIONS OR OPERATIONS

DEFINITION-The SQL Aggregate Functions are functions that provide mathematical operations. If you need to add, count or perform basic statistics, these functions will be of great help. In (Database Management) an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement such as a set, a bag or a list.

 USEFUL AGGREGATE FUNCTIONS

AVG () - Returns the average value form the the given table

COUNT () - Returns the number of rows

MAX () - Returns the largest value

MIN () - Returns the smallest value

SUM () - Returns the sum

1. AVG()- The AVG Function returns the average value for the column when applied to a column containing numeric data. The following is the syntax for the AVG Function.

SYNTAX- select avg (column name) from <table name>;

EG- select avg (losal) from salgrade;

2.COUNT()- The COUNT Function returns the number of rows in a specified result set. The following syntax is one form of the COUNT Function.

SYNTAX- select count(*) from <table name>;

EG- select count (*) from salgrade;

3.MAX() - The MAX Function returns the data item with the highest value for a column when applied to a column containing numeric data. If you apply the MAX Function to a CHARACTER value, it returns the last value in the sorted values for that column. The following syntax is for the MAX Function.

SYNTAX- select max (column name) from <table name> group by <col name>;

EG- select max (salary) from emp group by id;

4. MIN()- The MIN Function returns the data item with the lowest value for a column when applied to a column containing numeric data. If you apply the MIN Function to a CHARACTER value, it returns the first value in the sorted values for that column. The following syntax is for the MIN Function.

SYNTAX- select min (column name) from <table name> group by <col name>;

EG- select min (salary) from emp group by name;

5.SUM()- The SUM Function returns the sum of all values in the specified column. The result of the SUM Function has the same precision as the column on which it is operating. The following syntax is for the SUM Function.

SYNTAX- select sum (column name) from <table name> group by <col name>;

EG- select sum (salary) from emp group by name;