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

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-144
Examples of STDDEV
•
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;
Suppose that the WEATHER table contains these rows:
°
Find the standard deviation of the TEMPERATURE column by STATE:
SELECT state, STDDEV (temperature)
FROM weather GROUP BY state;
STATE (EXPR)
---------- --------------------
TX 1.88591308593750024E+001
CA 0.00000000000000000E+000
--- 2 row(s) selected.
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