VAR_POP/VARIANCE Functions

Description

The functions VARPOP and VARIANCE are used interchangeably and they return a variance of expression values for all rows. Only one expression is specified as a parameter. If the DISTINCT or UNIQUE keyword is inserted before the expression, they calculate the sample standard deviation after deleting duplicates; if the keyword is omitted or ALL, they calculate the sample standard deviation for all values.

The return value is a DOUBLE type. If there are no rows that can be used for calculating a result, NULL will be returned.

The following is a formula that is applied to the function.

VAR_POP = (1/N) * SUM( { x_I - AVG(x) }² )

• SUM: Sum
• AVG: Average

Warning In CUBRID 2008 R3.1 or earlier, the VARIANCE function worked the same as the VAR_SAMP Function.

Syntax

VAR_POP( [ DISTINCT | UNIQUE | ALL] expression )

• expression: Specifies an expression that returns a numeric value.
• ALL: Gets the variance for all values (default).
• DISTINCT or UNIQUE: Gets the variance of unique values without duplicates.

Example

CREATE TABLE test_table (d double);

INSERT INTO test_table VALUES(78), (63.65), (230.54), (32), (17.2), (195.7689), (57.57);

SELECT VAR_POP(d) FROM test_table;

                var_pop(d)

==========================

    5.886658366433878e+003

SELECT VAR_POP(POWER(d,2)+d*2+1) FROM test_table;

  var_pop( power(d, 2)+d*2+1)

=============================

       3.983875901862495e+008

TRUNCATE TABLE test_table;

SELECT VAR_POP(d) FROM test_table;

                var_pop(d)

==========================

                      NULL