Breaking News
You are here: Home / Notes / SEM-3 / RDBMS / Oracle AGGREGATE FUNCTIONS

Oracle AGGREGATE FUNCTIONS

AGGREGATE FUNCTIONS

 

1) AVG: Returns an average value of ‘n’, ignoring null values in a column.

 

     SYNTAX:

                     AVG ([<DISTINCT> | <ALL>] <n>)

   EXAMPLE:

                    

 SELECT AVG (CURBAL)”Average Balance” FROM ACCT_MSTR;

   OUTPUT:              Average Balance

————————

1100

2)  MIN: Returns a minimum value of expr.

SYNTAX:

MIN ([<DISTINCT> | <ALL>] <expr>)

EXAMPLE:

SELECT MIN(CURBAL)”Minimum Balance” FROM ACCT_MSTR;

OUTPUT:          Minimum Balance

————————

500

 

Two type of SQL function

Agrigate functions

 

Single Row function

single row functions

 

3)  COUNT(expr): Returns the number of rows where expr is not null.

SYNTAX:

COUNT ([<DISTINCT> | <ALL>] <expr>)

EXAMPLE:

SELECT COUNT(ACCT_NO)”No. of Accounts” FROM ACCT_MSTR;

OUTPUT:

No. of Accounts

————————

10

4) COUNT (*): Returns the no of rows in the table, including duplicates and those with nulls.

 

SYNTAX:

COUNT(*)

EXAMPLE:

SELECT COUNT(*)”No. of Records” FROM ACCT_MSTR;

OUTPUT:

No. of Records

————————

10

5)  MAX: Return the maximum value of expr.

 

SYNTAX:

MAX ([<DISTINCT> | <ALL>] <expr>)

EXAMPLE:

SELECT MAX(CURBAL)”Maximum Balance” FROM ACCT_MSTR;

 

OUTPUT:

Maximum Balance

————————

2000

6) SUM: Return the sum of the value of ‘n’.

 

SYNTAX:

SUM ([<DISTINCT> | <ALL>] <n>)

EXAMPLE:

SELECT SUM(CURBAL)”Total Balance” FROM ACCT_MSTR;

OUTPUT:

Total Balance

————————

11000

Download Paper Solutions from Below Link

About admin

Scroll To Top