Consider the following table definition and data:
CREATE TABLE Product_Life (Product_ID INTEGER, Product_class VARCHAR(30), Hours INTEGER); SELECT * FROM Product_Life;
The output from the SELECT statement is:
Product_ID Product_class Hours ----------- ------------------------------ ----------- 100 Bulbs 100 100 Bulbs 200 100 Bulbs 300
The following is the SQL definition of an aggregate UDF that calculates the standard deviation of the input arguments:
CREATE FUNCTION STD_DEV (i INTEGER) RETURNS FLOAT CLASS AGGREGATE (64) SPECIFIC std_dev LANGUAGE C NO SQL PARAMETER STYLE SQL NOT DETERMINISTIC CALLED ON NULL INPUT EXTERNAL NAME 'ss!stddev!stddev.c!f!STD_DEV'
The following query uses the aggregate UDF expression to calculate the standard deviation for the life of a light bulb.
SELECT Product_ID, SUM(Hours), STD_DEV(Hours) FROM Product_Life WHERE Product_class = 'Bulbs' GROUP BY Product_ID;
The output from the SELECT statement is:
Product_ID Sum(hours) std_dev(hours) ----------- ----------- ---------------------- 100 600 8.16496580927726E 001