RDBMS, ,
  • Aggregate functions are those functions that can be apply on multiple rows and returns signal result for all rows.
  • They are also known as grouping functions.
  • Oracle provides the following aggregate function.

Sum()

  • It return sum of all given values.
  • Syntax
 sum (val1, val2, …. Val n)
  • Example:
SELECT sum ( 10, 20, 30 ) FROM DUAL ;
  • Output:
60
  • Example:
SELECT sum ( salary ) FROM emp;
  • Output:
1,20,000

Max ()

  • It returns maximum value from a given set of value.
  • Syntax:
max (val1, val2, ..., valN);
  • Example:
SELECT max ( 10, 20, 30 ) FROM DUAL ;
  • Output:
30
  • Example:
SELECT max ( salary ) FROM emp ;
  • Output:
20,000

Min ()

  • It returns minimum value from a given set of value.
  • Syntax:
min (val1, val2, ..., valN);
  • Example:
SELECT min( 10, 20, 30 ) FROM DUAL ;
  • Output:
10
  • Example:
SELECT min( salary ) FROM emp;
  • Output:
5000

Avg ()

  • It returns average value from a given set of value.
  • Syntax:
avg ( value1, value2, ..., valueN ) ;
  • Example:-
SELECT avg ( 10, 20, 30 ) FROM DUAL;
  • Output:
20

Count ()

  • It returns total number of records of the database table.
  • It has two form as follow.
  • count (*):
  • it count total no of records including duplicate records as well as null values.
  • Syntax:
count (*) ;
  • Example:
SELECT count (*) FROM emp;
  • Output:
35
  • count <expr>:
  • it counts total number of records excluding null values.
  • Syntax:
count (expr) ;
  • Example:
SELECT count (emp_no) FROM emp;
  • Output:
30