Crazy Tattoos

Quick Understanding Of Oracle Number Functions

NUMBER FUNCTIONS IN ORACLE

• Particularly in business everything is guided by numbers
• •          Fortunately Oracle, provides functions which deal with numbers.
• •          Oracle functions deal with three classes of numbers:

            Single values, group values AND list values.

ü        Single value, is one number, such as 24.45.

ü        Group values, is all numbers in a column of a table.

ü        List values, is a series of numbers in columns of a row.

Lets best understand by an example consider the climate table below.

CityName       MinTemp        MaxTemp       DatedOn

------------------------------------------------------------------

Mumbai         33.33                40.5         12-mar-2010

Chennai         32.33                 42.50      12-mar-2010

Calcutta        34.45                 38.0        12-mar-2010

Delhi              36.89                46.89      12-mar-2010

Mumbai         33.45                 45.89      13-mar-2010

Single value, is one value in a column.

Group values, is values in a column, of all rows.for example all values of MinTemp column.

List Values, is collection of values in columns of a particular row. For example 33.33 and 40.5 are from the first row of the above table.

• Functions acting on single values.

Function                                                                         Description

-------------------------------------------------------------------------------------------------

Value1 + value2                        Here + function, adds value1 with value2.

Value1 – value2                        Here – function, Substracts value2 from value1.

Value1 * value2                        Here * function, multiplies value2 with value1.

Value1 / value2                        Here / function, Divides value1 with value2.

ABS(value)                     Returns ABSolute value, ie returns positive value. 

SIGN(value)                      Returns,

   1  if value positive,

                -1 if value negative,

      0 if value zero.

CEIL(value)                     Returns Integer, just above the given value or equal to the value.

FLOOR(value)                  Returns Integer, just below the given or equal to the value.

MOD(value,divisor)        Returns, reminder of value/divisor.

NVL(value,substitute)              Substitutes value, if value is null.

POWER(value,exponent)   Returns, Value raised to an exponent.

SQRT(value)                    Returns, Square root of value

TRUNC(value, precision)   Returns, Value truncated to precision.

ROUND(value,precision)    Returns, value Rounded to precision.

 

Before we see some examples, lets' understand what does dual mean. Dual is a dummy table in oracle database, which is used for testing sql statements.

Let us work some examples, making use of functions acting on single values. Here letters in blue are results given by oracle when respective query is executed

SQL>select 6 + 2 from dual;

6 + 2

--------

   8

SQL>select 6 - 2 from dual;

6 - 2

------

   4

SQL>select 6 * 2 from dual;

6 * 2

-------

   12

SQL>select 6 / 2 from dual;

6/2

----

  3

SQL>select ABS(-121) from dual;

ABS(-121)

--------------

       121

SQL>select ABS(121) from dual;

ABS(121)

-------------

     121

SQL> select ABS(-121) from dual;

 ABS(-121)                                                                      

----------------                                                                     

       121                                                                      

SQL> select sign(121) from dual;

 SIGN(121)                                                                     

----------------                                                                     

         1                                                                     

SQL> select sign(-121) from dual;

SIGN(-121)                                                                     

-----------------                                                                     

        -1                                                                      

SQL> select sign(0) from dual;

   SIGN(0)                                                                     

-----------------                                                                     

         0             

SQL> select CEIL(121.22) from dual;

CEIL(121.22)

--------------------

         122

SQL> select CEIL(-121.22) from dual;

CEIL(-121.22)

---------------------

         -121

SQL> select CEIL(121) from dual;

 CEIL(121)

-----------------

       121

SQL> select CEIL(-121) from dual;

CEIL(-121)

-----------------

      -121

SQL> select FLOOR(121.22) from dual;

FLOOR(121.22)

-----------------------

          121

 

SQL> select FLOOR(-121.22) from dual;

FLOOR(-121.22)

------------------------

          -122

SQL> select FLOOR(121) from dual;

FLOOR(121)

------------------

   121

SQL> select FLOOR(-121) from dual;

FLOOR(-121)

-------------------

       -121

SQL> select MOD(10,3) from dual;

 MOD(10,3)

----------------

         1

SQL> select MOD(10,2) from dual;

 MOD(10,2)

-----------------

         0

SQL> select MOD(10,0) from dual;

 MOD(10,0)

-----------------

        10

SQL> select MOD(0,10) from dual;

 MOD(0,10)

----------------

         0

SQL> select MOD(0,0) from dual;

  MOD(0,0)

---------------

         0

Consider  the climate table below

 

CityName           MinTemp       MaxTemp              DatedOn

-------------------------------------------------------------------------------

Secunderabad           27                33              12-feb-2008

Hyderabad               24                35              23-nov-2008

Ahmedabad              34                                        26-mar-2008

Gaziabad                 23                34              24-sep-2008

 

On observing the above table, we find that in 3rd row, value in, column MaxTemp is unavailable. Now by making use of NVL function we can substitute the unavailable data with some value.

Select CityName, NVL(MaxTemp,33.5) from dual;

CityName               MaxTemp

----- ----------------------------------

 Secunderabad          33

 Hyderabad                35 

 Ahmedabad              33.5

 Gaziabad                  34

Remember

ü       NVL function does not update the data base. It temporarily substitutes and displays on the screen.

SQL> select POWER (2, 3) from dual;

POWER (2,3)

-------------------

         8

SQL> select SQRT(4) from dual;

   SQRT(4)

-----------------

         2

SQL> select ROUND(34.4556,2) from dual;

ROUND(34.4556,2)

----------------------------

           34.46

SQL> select ROUND(34.4556,3) from dual;

ROUND(34.4556,3)

----------------------------

          34.456

SQL> select ROUND(-34.4556,2) from dual;

ROUND(-34.4556,2)

----------------------------

           -34.46

SQL> select TRUNC(34.4556,2) from dual;

TRUNC(34.4556,2)

---------------------------

           34.45

SQL> select TRUNC(34.4556,3) from dual;

TRUNC(34.4556,3)

---------------------------

          34.455

• Functions acting on Group of values.

These Functions act, on values, of a column, in all rows, of a table.

The  most commonly used functions are

 

Function                                                   Description

-------------------------------------------------------------------------------------------------

AVG(ColumnName)                       Average of all values in the column.

COUNT(ColumnName)         number - of rows or of values in column.

SUM(ColumnName)            sum total, of all values in the column.

MIN(ColumnName)                        Returns least of all values in that column.

MAX(ColumnName)                       Returns highest of all values in that column.

STDDEV(Columnname)                 Usual statistical meaning.

VARIANCE(ColumnName)              Usual statistical meaning,

 

Lets' consider the emp table, from the scott/tiger  schema of oracle database

EMPNO   ENAME    JOB                MGR   HIREDATE     SAL        COMM   DEPTNO

7369        SMITH         CLERK        7902  17-DEC-80     800           20              20

7499        ALLEN         SALESMAN   7698  20-FEB-81     1600         300            30

7521        WARD         SALESMAN   7698  22-FEB-81     1250          500           30

7566        JONES         MANAGER    7839   02-APR-81       2975         20             20

7654        MARTIN       SALESMAN   7698  28-SEP-81       1250         1400          30

7698        BLAKE         MANAGER    7839   01-MAY-81       2850         30            30

7782        CLARK         MANAGER    7839   09-JUN-81        2450                  10             10

7788        SCOTT        ANALYST    7566  19-APR-87        3000                  20             20

7839        KING           PRESIDENT           17-NOV-81        5000        10             10

7844        TURNER       SALESMAN   7698  08-SEP-81        1500          0       30             20

7876        ADAMS        CLERK        7788  23-MAY-87       1100          20           20

7900        JAMES         CLERK        7698  03-DEC-81        950            30           30

7902        FORD          ANALYST    7566  03-DEC-81        3000          20           20

7934        MILLER        CLERK        7782  23-JAN-82        1300                    10           10

 

Lets work some examples, the letters in blue are the results returned by the oracle database,in reply to a query

Find the maximum salary paid

SQL> select MAX(sal) from emp;

MAX(SAL)

--------------

    5000

Find the minimum salary paid

SQL> select MIN(sal) from emp;

  MIN(SAL)

---------------

       800

Find the average salary paid

SQL> select AVG(sal) from emp;

  AVG(SAL)

-----------------

2073.21429

Find the total amount paid as salaries to the employees

SQL> select SUM(sal) from emp;

  SUM(SAL)

-----------------

     29025

COUNT functions usage is situational:

• Is used to count the number of rows in a table, if the argument is *(asterisk)

 SQL> select COUNT(*) from emp;

  COUNT(*)

------------------

        14

• Is used to count number of available data in a column of a table.

Find the number of,  NOT NULL values, in the column mgr of emp table?

SQL> select COUNT(mgr) from emp;

COUNT(MGR)

--------------------

        13

• DISTINCT key word, can be used, with every GROUP Function, but its usage is more felt, with COUNT function. Let us see apply DISTINCT to find, how many types of jobs are there in emp table

SQL> select COUNT(DISTINCT JOB) from emp;

COUNT(DISTINCTJOB)

----------------------------------

                 5

There are 5 types of JOB are in emp table.

 

Lets write a query to find number, of NULL values in mgr column of emp table

SQL> select COUNT(*)-COUNT(mgr) from emp;

COUNT(*)-COUNT(MGR)

-----------------------------------

                  1

There is one NULL, in mgr column, of emp table.

Remember

ü       Asterisk can not be used with any other GROUP function, other than COUNT.

ü       DISTINCT finds its usage mostly with COUNT function.

ü       COUNT can be used either with character column or number column, where as other GROUP functions can be used only with numerical data.

ü       By now you should have observed, COUNT does not perform any arithmetic operations with the values of a column but it either counts number of values in a column or number rows in a table.

ü       The alternative to DISTINCT is ALL, which is the default.

• Functions acting on list of values

These functions act on values, in two or more columns, of a row, in a table

The most frequently used functions are described in table below

 

FUNCTION                                                                   DESCRIPTION

-----------------------------------------------------------------------------------------------------------------------------

LEAST(value1,value2,value3,..)                 Returns least value,among the values in the argument list.

GREATEST(value1,value2,value3,..)    Returns greatest value, among the values in the argument                

                                                               List

Consider the StudentMarks  table below, with columns as student name, marks in physics, maths,

Chemistry

StudentName             MathMarks   PhysicsMarks             ChemistryMarks

------------------------------------------------------------------------------------------------------

Lova Raju                       67                  76                          89

Siri                               100                 99                          99

Viji                                99                  90                          88

Koti                               98                   88                                   87

 

Now write a query that would display student name and highest mark obtained, among the three subjects

Sql>select StudentName,Greatest(MathMarks,PhysicsMarks,ChemistryMarks) as HighestMark from StudentMarks;

ENAME      HIGHESTMARK

-------------------------------------

Lova Raju             89

Siri                      100

Viji                        99

Koti                       98

 

Write a query that would display student name and the least mark obtained, among the three subjects

Sql> select StudentName, LEAST(MathMarks,PhysicsMarks,ChemistryMarks) as LowestMark from StudentMarks;

• NULLs in -List Value Functions  and Single Value Functions:
  • List value functions treat NULL values same as that of single value functions.
  • Single value functions produce NULL result, if any value is NULL.
  • List value functions produce NULL result, if any value in the list is NULL
• NULLS in - GROUP Value Functions :
  • Group value functions treat NULL values differently than single value functions.
  • Group value functions ignore NULL values and go ahead with the result.

            Remember

ü       Group value functions, ignore NULL values

ü       Single value and List value functions, don't  ignore NULL values.

 

About the Author

The author is a trainer in oracle and Java

u can get in toch with me on raju.allu@yahoo.com

Highline Excel Class 09: SUMIF Function Formula 3 Examples

« Previous12345...399400Next »