SQL Multi Row functions – SQL Server Tutorial

MULTI ROW FUNCTIONS   :–

{NULL VALUE ARE NOT CALCULATED}

1. These functions are also calld “Aggregate functions” (Or) Group functions.
2. All these multi row functions will process multiple records.
3. Applied on group of records and returns one value from the entire group.

MAX ( ) Function in SQL:-

• Returns maximum value of a given expression.

Syntax   :

Max(expr)

• Select max(sal) from emp
• Output → 5000

Display max salary of 20^th department  :-

• Select max(sal),from emp Where deptno=20
• Output → 3000

• Select max(sal +isnull(comm,0)) from emp
• Output → 5000

• Select max (ename) from emp
• Output → word   {based on Ascii}

• Select max(hiredate)from emp
• Output → 1983

Min ( ) Function in SQL:-

• Returns minimum value of a given expression
• Syntax :-min(expr).
• Select min(sal) from emp
• Output → 36025

Note  :– We can not apply this functions to varchar & data columns, Apply only on numeric columns.

display total salaries paid to manager

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

avg  ( ) Function in SQL:-

• It returns avg value of 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:-

• 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 integer .

count_big

• The return type of count is big_integer.

Select count_big(*) from emp.