Aggregate And Numeric Functions.

                  Aggregate And Numeric Functions

SQL has many built-in functions for performing processing on string or numeric data.

➥AGGREGATEE FUNCTIONS:-

   ➧Average function:Returns average value of 'n', ignoring null values.

        Example: SELECT AVG(sell_price) from product;

    ➧COUNT :Counts rows in a specified table or view.
       Example:SELECT  COUNT(*) FROM products;

    ➧MIN :Gets the minimum value in a set of values.
        Example:SELECT MIN(unitsinstock) FROM products;

    ➧MAX :Gets the maximum value in a set of values.
       Example:SELECT  MAX(unitsinstock)FROM  products;

    ➧SUM :Calculates the sum of values
      Example:SELECT SUM(bal_due) from client_master;
       

➥NUMERIC FUNCTIONS:-

ABS(number)

Returns the absolute positive value of an expression.
Syntax:

ABS(expression)

Example:

SELECT ABS(-1.0), ABS(0.0), ABS(1.0)

Output:

1.0   .0   1.0

CEIL(number)

Returns the smallest integer greater than, or equal to, the specified numeric expression.
Syntax:

CEILING(expression)

Example:

SELECT CEILING($223.45), CEILING($-223.45), CEILING($0.0)

Output:

224.00   -223.00   0.00

FLOOR(number)

Returns the largest integer less than, or equal to, the specified numeric expression.
Syntax:

FLOOR(expression)

Example:

SELECT FLOOR($223.45), CEILING($-223.45), CEILING($0.0)

Output:

223.00   -224.00   0.00

MOD(number, divisor)

Returns the remainder of the division from 2 integer values.
Syntax:

MOD(dividend, divisor)

Example:

SELECT MOD(20,3)

Output:

2

POWER(number, power)

Returns the exponential value for the numeric expression.
Syntax:

POWER(number, power)

Example:

SELECT POWER(2.0, 3.0)

Output:

8.0

SIGN(number)

Returns the sign i.e. positive or negative value for the numeric expression. It returns -1 for negative expressions, a value of 0 for zero
Syntax:

SIGN(number)

Example:

SELECT SIGN(4)

Output:

1

ROUND(number, precision)

Returns the numeric value rounded off to the next value specified.
Syntax:

ROUND(number, number of places)

Example:

SELECT ROUND(1.3456, 2)

SQRT(number)
Returns the square root value of the expression.
Syntax:

SQRT(number)

Example:

SELECT SQRT(4.0)

Output:

2.0

TRUNC(number, precision) 
Returns a numeric value that truncate to the specific places
Syntax:

TRUNCATE(number,places)

Example:

SELECT TRUNCATE(1.3456, 2)

Output:

1.34

.