Multi Row Function Tips
=> Will process group of records and returns one value from the group.
=> These functions are also called as "Aggregate Functions" or "Group Functions".
(i) Max():
=> It returns maximum value of a given expression.
Syntax:
=> MAX( expr )
Examples:
1. Display the maximum salary paid to the employee.
=> Select MAX( sal ) from emp;
2. Display the Maximum experienced employee.
=> Select MAX( sysdate - hiredate ) from emp;
3. Display the maximum salary of the employee who is working in 30th dept.
=> Select Max( sal ) from emp where deptno = 30;
(ii) Min():
=> It returns minimum value of the given expression.
Syntax:
=> MIN( expr )
Examples:
1. Display the minimum salary paid to the employee.
=> Select MIN( sal ) from emp;
2. Display the minimum experienced employee.
=> Select MIN( sysdate - hiredate ) from emp;
3. Display the minimum salary of the employee who is working in 30th dept.
=> Select MIN( sal ) from emp where deptno = 30;
(iii) SUM():
=> It returns sum of the expression.
Syntax:
=> SUM( expr )
Examples:
1. Get the total paid to each Dept ?
=> Select SUM( sal ) from Emp Group By dept;
(iv) AVG():
=> It returns the Average of the expression.
Syntax:
=> AVG( expr )
Examples:
1. Get the Average sal of all the employees ?
=> Select AVG( sal ) from Emp;
(v) COUNT():
=> It returns the total no of records present in a column.
=> Count ignores NULL values.
=> Count will count duplicate values also.
To ignore it, use DISTINCT keyword.
" COUNT( DISTINCT empno ) "
Syntax:
=> COUNT( expr )
Examples:
1. Count the no of employees working in dept no 30.
=> Select count(*) from emp where deptno = 30;