SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—523725-004

9-175

Considerations for VARIANCE

Weighted Average

The weighted average vw of v

and w

is defined as:

where v

is the i-th value of expression, w

is the i-th value of weight, and N is the

cardinality of the result table.

Data Type of the Result

The data type of the result is always DOUBLE PRECISION.

Operands of the Expression

The expression includes columns from the rows of the SELECT result table—but

cannot include an aggregate function. These expressions are valid:

VARIANCE (SALARY)

VARIANCE (SALARY * 1.1)

VARIANCE (PARTCOST * QTY_ORDERED)

Nulls

VARIANCE is evaluated after eliminating all nulls from the set. If the result table is

empty, VARIANCE returns NULL.

FLOAT(54) and DOUBLE PRECISION Data

Avoid using large FLOAT(54) or DOUBLE PRECISION values as arguments to

VARIANCE. If SUM(x * x) exceeds the value of 1.15792089237316192e77 during the

computation of VARIANCE(x), then a numeric overflow occurs.

∑

⋅