Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

function

SQL VARIANCE Function

by

Oracle VARIANCE FunctionSQL VARIANCE Function returns variance of available records. Oracle VARIANCE Function accepts column/formula as parameter for Variance calculation.

SQL VARIANCE Function Syntax

SELECT VARIANCE(numeric column / formula)
FROM table_name
WHERE conditions;

SQL VARIANCE Function Examples

Suppose we have a table named “Employee” with the data as shown below.

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

SQL VARIANCE examples are mentioned below

SQL VARIANCE Function – Simple Usage

A simple usage of Oracle VARIANCE Function will be to fetch the variance of a table column.

For example, below SQL VARIANCE query returns variance of salary of employees

SELECT VARIANCE(salary) "Oracle VARIANCE"
FROM employee;

The above Oracle VARIANCE query returns ‘82000000’ as variance of salaries in ‘employee’ table.

Note: We have aliased VARIANCE(salary) as Oracle VARIANCE.

SQL VARIANCE Function – Using Formula Example

Oracle VARIANCE Function accepts formula also as parameter.

For example, the Oracle VARIANCE query returns variance of salary*(commission/100) from employee table.

SELECT salary*(commission/100) "SQL VARIANCE"
FROM employee;

Above Oracle VARIANCE query returns ‘3663333.33333333’ as variance of salary*(commission/100) from employee table.

Note: We have aliased salary*(commission/100) as “SQL VARIANCE”.

SQL AVG/Average Function

by

SQL AVG FunctionOracle SQL Average Function or SQL AVG Function returns average of available records. Oracle Average Function or Oracle AVG Function accepts column/formula as parameter.

SQL Average Function Syntax

SELECT AVG(numeric column / formula)
FROM table_name
WHERE conditions;

SQL Average Function Examples

Suppose we have a table named “Employee” with the data as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

We will learn using Oracle SQL AVG Function below.

SQL Average Function – Oracle AVG Simple Usage

Oracle AVG Function query below returns the average of salaries of all employees from ’employee’ table.

SELECT AVG(salary) "Oracle SQL Average"
FROM employee;

Above Oracle Average Function query returns ‘24000’ as the average of salaries from employee table.

Note: We have aliased AVG(Salary) as Oracle SQL Average.

SQL Average Function – SQL AVG Using Formula Example

Oracle SQL Average Function accepts formula for calculation.

The SQL AVG Function query below return Average of Salary*(commission/100) of ‘Sales’ department.

SELECT AVG(salary*(commission/100)) "SQL AVG"
FROM employee
WHERE department  = 'Sales';

Above Oracle Average Function query returns ‘2100’ as the average of Salary*(commission/100) of ‘Sales’ department from ’employee’ table.

Note: We have aliased AVG(salary*(commission/100)) as SQL AVG.

SQL Average Function – Using SQL Group By Clause

SQL AVG Function can be used with the SQL Group By Clause.

For example, the Oracle AVG Function query below return the average salary in each department.

SELECT department, AVG(salary) Total_Salary
FROM employee
GROUP BY department;

Above Oracle SQL Average Function query returns following data:

Department Average_Salary
Sales 21000
IT 24000
Support 30000

As we can understand that by using SQL GROUP BY Clause with Oracle Average Function we can fetch average salary of employee(s) in every unique department.

SQL SUM Function

by

SQL SUM FunctionSQL SUM Function returns total or summed up value of the available records.Oracle SUM Function can accept a column or field of database table or a formula as parameter.

SQL SUM Function Syntax

SELECT SUM(numeric column / formula)
FROM table_name
WHERE conditions;

SQL SUM Function Examples

Suppose we have a table named “Employee” with the data as shown below.

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Now we will see what Oracle SUM Function does when used in different scenarios

SQL SUM Function – SQL SELECT SUM Example

Suppose we wish to view total ‘Salary’ of all employees from ‘employee’ table.

SQL SELECT SUM query below will return total salary given to all employees.

 SELECT SUM(salary) Total_Salary
 FROM employee;

SQL SELECT SUM Function query above returns ‘120,000’ because total salaries of all employees is ‘120,000’.

Note: SQL SELECT SUM Function query above uses “Total_Salary” as alias name for SUM(Salary) query result column.

SQL SUM Function -Using Formula Example

SQL SUM Function can also accept a formula as parameter for summing up values.

For example, Oracle SUM Function query below returns total commission of employees from ‘Sales’ department.

 SELECT SUM(salary*(commission/100)) Commission_Amount
 FROM employee
 WHERE department  = 'Sales';

SQL SUM Function query above returns ‘4200’ as commission of ‘Sales’ department.

Note: Oracle SUM Function query above also uses the SQL WHERE Clause.

SQL SUM Function -SQL SUM Group By Example

SQL SUM Function can be used with SQL GROUP BY Clause to form SQL SUM Group By query.

For example, SQL SUM Group By Function query below returns total salary for each department.

 SELECT department, SUM(salary) Total_Salary
 FROM employee
 GROUP BY department;

SQL SUM Group By query above returns following data:

Department Total_Salary
Sales 42000
IT 48000
Support 30000

By using the SQL Group By Clause we have fetched the total salary of every unique department.

TANH Function in Oracle SQL – PLSQL

by

The TANH function in Oracle SQL / PLSQL is used to calculate hyperbolic tangent of numeric value where numeric value is provided in radian format.

It’s mathematically equal to TANH(N).

Syntax for the TANH function in Oracle SQL / PLSQL is:

SELECT TANH(radian)
FROM table_name;

Let’s take an example for understanding:

Suppose we want to get the hyperbolic tangent of 30 degree.

SELECT TANH(30*22/(7*180))
FROM dual;

The output of the above statement will be:

TANH(30*22/(7*180))
0.480634857866646

Notice that we have changed 30 degrees into radians while passing it in TANH function.

TAN Function in Oracle SQL – PLSQL

by

The TAN function in Oracle SQL / PLSQL is used to calculate tangent of numeric value where numeric value is provided in radian format.

It’s mathematically equal to TAN(N).

Syntax for the TAN function in Oracle SQL / PLSQL is:

SELECT TAN(radian)
FROM table_name;

Let’s take an example for understanding:

Suppose we want to get the tangent of 30 degree.

SELECT TAN(30*22/(7*180))
FROM dual;

The output of the above statement will be:

TAN(30*22/(7*180))
0.57763130100345

Notice that we have changed 30 degrees into radians while passing it in TAN function.