SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-169
Examples of VARIANCE
Examples of VARIANCE
•
Compute the variance of the salary of the current employees:
SELECT VARIANCE(salary) AS Variance_Salary
FROM persnl.employee;
VARIANCE_SALARY
-------------------------
1.27573263588496116E+009
--- 1 row(s) selected.
•
Compute the variance of the cost of parts in the current inventory:
SELECT VARIANCE (price * qty_available)
FROM sales.parts;
(EXPR)
-------------------------
5.09652410092950336E+013
--- 1 row(s) selected.
•
Suppose that your database includes a WEATHER table, which is created by using
SQLCI in this way:
CREATE TABLE $db.mining.weather
( city VARCHAR (20) NO DEFAULT NOT NULL
,state CHAR (2) NO DEFAULT NOT NULL
,date_weather DATE NO DEFAULT NOT NULL
,temperature NUMERIC (3) SIGNED
,weight NUMERIC (2) UNSIGNED
,PRIMARY KEY (city, state, date_weather))
CATALOG $db.mining
ORGANIZATION KEY SEQUENCED;
After the table is created, you can insert the mapping into the OBJECTS table by
using MXCI in this way:
CREATE SQLMP ALIAS db.mining.weather $db.mining.weather;
For these examples, the WEATHER table contains these rows:
CITY STATE DATE_WEATHER TEMPERATURE WEIGHT
Austin TX 1997-01-01 50 1
Austin TX 1997-01-02 40 1
Austin TX 1997-01-03 60 2
Austin TX 1997-01-04 84 2
Cupertino CA 1997-01-01 65 1
Cupertino CA 1997-01-02 65 2
Cupertino CA 1997-01-03 65 2
Cupertino CA 1997-01-04 65 1