How do you Create a Calculation in Oracle Analytics?

Calculation in Oracle

Numerous numeric functions that accept numeric input and reliably return numeric values are available in the Oracle Database. These functions are essential for carrying out a variety of computations and data transformations inside PL/SQL blocks and SQL statements.

In order to properly illustrate these functions, let’s start by generating a table called MATH_DATA and adding a range of numeric values to it, such as decimal, positive, and negative numbers, in addition to NULL entries. We will use this table as the basis for the next examples.

Table Creation and Data Insertion for Numeric Functions:

CREATE TABLE MATH_DATA (
    ID          NUMBER PRIMARY KEY,
    DESCRIPTION VARCHAR2(50),
    VALUE_ABOVE NUMBER,
    VALUE_BELOW NUMBER,
    VALUE_EMPTY NUMBER
);
INSERT INTO MATH_DATA (ID, DESCRIPTION, VALUE_ABOVE, VALUE_BELOW, VALUE_EMPTY) VALUES (1, 'WHOLE NUMBER', 11, -22, NULL);
INSERT INTO MATH_DATA (ID, DESCRIPTION, VALUE_ABOVE, VALUE_BELOW, VALUE_EMPTY) VALUES (2, 'LOW DECIMAL', 33.33, -44.44, NULL);
INSERT INTO MATH_DATA (ID, DESCRIPTION, VALUE_ABOVE, VALUE_BELOW, VALUE_EMPTY) VALUES (3, 'MID DECIMAL', 55.5, -55.5, NULL);
INSERT INTO MATH_DATA (ID, DESCRIPTION, VALUE_ABOVE, VALUE_BELOW, VALUE_EMPTY) VALUES (4, 'HIGH DECIMAL', 66.666, -77.777, NULL);
COMMIT;
-- Display the initial data
SELECT * FROM MATH_DATA ORDER BY ID;

Output of SELECT * FROM MATH_DATA:

        ID DESCRIPTION                                        VALUE_ABOVE VALUE_BELOW VALUE_EMPTY
---------- -------------------------------------------------- ----------- ----------- -----------
         1 WHOLE NUMBER                                                11         -22        (null)
         2 LOW DECIMAL                                               33.33      -44.44        (null)
         3 MID DECIMAL                                                55.5       -55.5        (null)
         4 HIGH DECIMAL

This MATH_DATA table contains values with diverse characteristics. For instance, the ‘WHOLE NUMBER’ row has no decimal parts, while ‘LOW DECIMAL’, ‘MID DECIMAL’, and ‘HIGH DECIMAL’ rows feature different decimal values, which is particularly relevant for ROUND and TRUNC functions. The VALUE_ABOVE column holds exclusively positive numbers, VALUE_BELOW contains only negative numbers, and VALUE_EMPTY is intentionally set to NULL.

ABS (Absolute Value)

A number’s absolute value is returned by the ABS function. Regardless of a number’s initial sign, its absolute value guarantees that it is always a non-negative number. Any numeric datatype or nonnumeric datatype that can be implicitly transformed to a numeric datatype can be passed to this function, which returns the same datatype as the numeric input.

Format: ABS(value).

Example: Let’s apply the ABS function to both positive and negative numbers from our MATH_DATA table to demonstrate its effect.

SELECT
    ID,
    DESCRIPTION,
    VALUE_ABOVE,
    ABS(VALUE_ABOVE) AS "Absolute_Above",
    VALUE_BELOW,
    ABS(VALUE_BELOW) AS "Absolute_Below"
FROM MATH_DATA
WHERE ID IN (1, 4) -- Showing for a whole number and a high decimal to illustrate
ORDER BY ID;

Output:

        ID DESCRIPTION VALUE_ABOVE ABSOLUTE_ABOVE VALUE_BELOW ABSOLUTE_BELOW
---------- ----------- ----------- -------------- ----------- --------------
         1 WHOLE NUMBER          11             11         -22             22
         4 HIGH DECIMAL      66.666         66.666      -77.777

ABS(146) yields 146, whereas ABS(-30) yields 30, as the output illustrates. In addition to ABS, the SIGN function provides the sign of a number (-1, 0, or 1) without supplying the magnitude.

ROUND (Round to Nearest)

The number that is returned by the ROUND function has been rounded to a predetermined number of decimal places. The number is rounded to 0 decimal places (the closest integer) if the precision argument is left out. Additionally, a negative precision value rounds numbers to the left of the decimal point. The rounding of NUMBER numbers takes place away from zero, for example, 55.5 rounds to 56 and -55.5 rounds to -56. But for values of BINARY_FLOAT and BINARY_DOUBLE, the algorithm rounds to the closest even number.

Format: ROUND(value, precision).

Example: Let’s demonstrate the ROUND function with various precision settings, including negative precision, using data from the MATH_DATA table.

SELECT
    ID,
    DESCRIPTION,
    VALUE_ABOVE,
    ROUND(VALUE_ABOVE, 1) AS "Round_One_Decimal",
    ROUND(VALUE_ABOVE, -1) AS "Round_Tens_Place",
    ROUND(55.5, 0) AS "Round_Point_Five_Up" -- Using a literal to demonstrate a specific rounding case
FROM MATH_DATA
WHERE ID = 4; -- Using 'HIGH DECIMAL' for clearer demonstration of decimal rounding

Output:

        ID DESCRIPTION VALUE_ABOVE ROUND_ONE_DECIMAL ROUND_TENS_PLACE ROUND_POINT_FIVE_UP
---------- ----------- ----------- ----------------- ---------------- -------------------
         4 HIGH DECIMAL      66.666             66.7

ROUND(15.193,1) produces 15.2, while ROUND(15.193,-1) produces 20. When enormous populations or monetary amounts need to be rounded to the closest millions, billions, or trillions, as in economic reporting, using negative precision for rounding can be especially helpful.

TRUNC (Truncate)

To a predetermined number of decimal places, an integer can be truncated using the TRUNC function. The number is truncated to the nearest integer, or 0 decimal places, if the precision option is left off. Like ROUND, accuracy can also be negative, truncating numbers to the left of the decimal point. The main difference between ROUND and TRUNC is that the former only discards precision digits without rounding them.

Format: TRUNC(value, precision).

Example: Let’s observe TRUNC in action with different precision settings, highlighting its non-rounding behavior.

SELECT
    ID,
    DESCRIPTION,
    VALUE_ABOVE,
    TRUNC(VALUE_ABOVE, 1) AS "Trunc_One_Decimal",
    TRUNC(VALUE_ABOVE, -1) AS "Trunc_Tens_Place",
    TRUNC(55.5, 0) AS "Trunc_Point_Five" -- Using a literal for direct comparison with ROUND
FROM MATH_DATA
WHERE ID = 4; -- Using 'HIGH DECIMAL' for demonstration

Output:

        ID DESCRIPTION VALUE_ABOVE TRUNC_ONE_DECIMAL TRUNC_TENS_PLACE TRUNC_POINT_FIVE
---------- ----------- ----------- ----------------- ---------------- ----------------
         4 HIGH DECIMAL      66.666             66.6

For 55.5, ROUND(55.5) results in 56, whereas TRUNC(55.5) results in 55. Similarly, for negative numbers, ROUND(-55.5) is -56, while TRUNC(-55.5) is -55. The TRUNC (number) function consistently returns n1 truncated to n2 decimal places.

MOD (Remainder)

A division operation’s residue is returned by the MOD function. In order to determine the remainder, it divides a value by a divisor. In the case where the divisor is zero or negative, the MOD(value, divisor) result is zero. The original value is returned by the MOD function if the divisor is greater than the value being divided. The function MOD(value, 1) is a practical way to verify whether a given number is an integer and returns 0 if it is.

Format: MOD(value, divisor).

Example: Here are several examples of MOD, using both literal values and data from MATH_DATA, to illustrate its various behaviors.

SELECT
    MOD(23, 6) AS "Mod_23_Div_6",       -- 23 divided by 6 is 3 with a remainder of 5
    MOD(VALUE_ABOVE, 3) AS "Mod_Value_Above_Div_3", -- E.g., for ID=4, 66.666 / 3
    MOD(22, 23) AS "Mod_Divisor_Larger", -- If divisor > value, result is value
    MOD(50, 10) AS "Mod_No_Remainder",   -- No remainder
    MOD(VALUE_ABOVE, 1) AS "Mod_Is_Integer" -- Test if it's an integer (returns 0 if true)
FROM MATH_DATA
WHERE ID = 4; -- Using 'HIGH DECIMAL' for demonstration

Output:

MOD_23_DIV_6 MOD_VALUE_ABOVE_DIV_3 MOD_DIVISOR_LARGER MOD_NO_REMAINDER MOD_IS_INTEGER
------------ --------------------- ------------------ ---------------- ----------------
           5                 0.666                 22 

From the examples, MOD(100,10) yields 0, MOD(22,23) yields 22, and MOD(10,3) yields 1. It’s important to note that the REMAINDER function, while similar to MOD, calculates the remainder using ROUND in its formula, in contrast to MOD which uses FLOOR. For instance, MOD(10, 2.8) results in 1.6, whereas REMAINDER(10, 2.8) results in -1.2.

SQRT (Square Root)

A non-negative number’s square root is returned by the SQRT function. For calculations involving mathematics or science, this function is especially helpful.

Format: SQRT(value).

Example: Let’s calculate the square root of various numbers to demonstrate the SQRT function.

SELECT
    SQRT(64) AS "Sqrt_64",
    SQRT(VALUE_ABOVE) AS "Sqrt_Value_Above", -- E.g., for ID=4, SQRT(66.666)
    SQRT(4) AS "Sqrt_4"
FROM MATH_DATA
WHERE ID = 4; -- Using 'HIGH DECIMAL' for demonstration

Output:

   SQRT_64 SQRT_VALUE_ABOVE     SQRT_4
---------- ---------------- ----------
         8       8.16492498          2

As can be seen, SQRT(64) produces 8, SQRT(66.666) produces 8.16492498, and SQRT(4) produces 2. Because the database does not handle imaginary numbers, it is important to keep in mind that trying to find the square root of a negative number would result in an Oracle error. If the input n is negative for binary floating-point numbers (BINARY_FLOAT or BINARY_DOUBLE), the function will return NaN, which is not a number.