Multi-Row Function in SQL

Introduction to Multi-Row Function In SQL

The multi-row function in SQL is used to retrieve data per set of rows at the time when we work on the group by clause we use the Multi-Row Function.

Types of Multi-Row Functions are

• Maximum(Max)
• Minimum(MIN)
• Average(Avg)
• Sum 
• Count

MAX ( ) Function in SQL:-

Returns the maximum value of a from the given data.

Syntax   :

Max(expression)

For example:-

Select max (e_name) 

from employees;

Output

5000

Display max salary of whose department_id=20

Select min(salary)

from employees where department_id=20;

Output → 3000

• Multiple function using Max:-

Select max(salary +isnull(comm,0)) 

from emp

Output → 5000

• Max _function on Charachter

Select max (e_name) 

from employees

Output → word   {based on Ascii}

Max _function on Date

Select max(hire_date)

from employees

Output → 1983

Min ( ) Function in SQL:-

Returns the minimum value of a given data.

Syntax:-

min(expression).

For Example:-

Select min(salary)

from employees

Output → 36025

Note:– We can not apply this function to varchar2 & data columns, Apply only on columns that have the numeric data.

display total salary paid to the manager

Select sum(sal) from emp Where job ‘manager’;

avg  ( ) Function in SQL:-

It returns avg value of the given expression

Select avg (sal) from emp;

Output → 2250.725

Select ceiling (avg(sal)) from emp;

Output → 2251  (named value)

Note  :- This function also we can’t apply on varchar & date column.

Count ( ) Function in SQL:-

Count no of values present in a column

Select count(empno) from emp

Output → 16

count * ( ) Function in SQL:-

Count ( ) Function in SQL:-

Count no of values present in a column

Select count(empno) from emp

Output → 16

count * ( ) Function in SQL:-

It returns no of records in the table

Select count (*) from emp

Output → 16

count_big(*) Function in SQL:-

Count

the return type of count is an integer .

count_big

The return type of count is big_integer.

Select count_big(*) from emp.

Conclusion:-

Conclusion:-

In this blog, you will learn all the types of multi-row functions and there uses.