VAR_SAMP Function in Oracle SQL – PLSQL

The VAR_SAMP Function in Oracle SQL / PLSQL is used to calculate the sample variance of a set of numbers.

Syntax for the using the VAR_SAMP Function in Oracle SQL / PLSQL is:

SELECT VAR_SAMP(expression_1)
FROM table_name;

  • expression_1 is numeric expression
  • The VAR_SAMP function in Oracle SQL / PLSQL will eliminate all the NULL values before calculation.

Examples:

Using VAR_SAMP Function in Oracle SQL / PLSQL SELECT statement:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales  
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support  
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Support  

If we write our VAR_SAMP Function query as:

SELECT VAR_SAMP(salary)
FROM employee;

We will get ‘76285714.2857143’ as output.


Tagged , , , , , , , , , , , . Bookmark the permalink.