Multi Row Functions | Aggregate functions | Types of Multiple Row Functions in Oracle | Avg | Count | Max | Min | Sum

Multi Row Functions / Aggregate Functions

Multiple Row Functions in Oracle

 SQL Functions:

What is a function?

Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format of their arguments. This format enables them to operate on zero, one, two, or more arguments:

Syntax: function (argument, argument, ...)

SQL Functions:

Functions are classified into two types:

•       System defined Functions

•       User Defined Functions.

System Defined Function:

•       A Function which is defined by System is known as System  defined Function.

User Defined Function:

•       A Function which is defined by User is known as User  defined Function

There are Two types of functions in oracle:

1.Single-Row Functions.

2.Multi-Row Functions.

•       The Multiple Row Functions in Oracle are used to return either group of values (or) a single value. These functions are basically operated on a set of rows and return one result or one result per group.

This is a powerful feature provided by oracle because these Multiple Row Functions allow us to generate subtotals, max. min, sums, and averages within the SQL that is retrieving the data. The Multiple row function in Oracle is also called group functions or it is also called aggregate functions

 

Types of Multiple Row Functions in Oracle

•       AVG: It retrieves the average value of the number of rows in a table by ignoring the null value.

•       COUNT: It retrieves the number of rows (count all selected rows using *, including duplicates and rows with null values).

•       MAX: It retrieves the maximum value of the expression, ignores null values.

•       MIN: It retrieves the minimum value of the expression, ignores null values.

•       SUM: It retrieves the sum of values of the number of rows in a table, it ignores null values.

MAX()

MAX ( ) Function in SQL:-

Returns maximum value of a given expression.

Syntax   :

                        select max(column) from table_name;

Examples:

   Select max(sal) from emp;

   --Output → 5000

Q : Display max salary of 20th department ?

•       Select max(sal),from emp Where deptno=20;

--Output → 3000

•       Select max (ename) from emp;

--Output → word   {based on Ascii}

•       Select max(hiredate)from emp;

--Output → 1983 

MIN()

Min ( ) Function in SQL:-

Returns minimum value of a given expression

Syntax :-   select min(column) from table_name;

EXAMPLE:-

Select min(sal) from emp;

--Output → 36025

AVG()

avg  ( ) Function in SQL:-

It returns avg value of given expression

Syntax :-   select Avg(column) from table_name;

EXAMPLE:-

Select avg (sal) from emp;

--Output → 2250.725

COUNT()  and COUNT(*)

•       Count ( ) Function in SQL:-

Count no of values present in a column

Syntax :-   select Count(column) from table_name;

EXAMPLE:-

Select count(empno) from emp;

--Output → 16

•       count * ( ) Function in SQL:-

It returns no of records in the table

Syntax :-   select Count*(column) from table_name;

EXAMPLE:-

Select count (*) from emp;

--Output → 16