CREATE DERBY AGGREGATE statement The CREATE DERBY AGGREGATE statement creates a user-defined aggregate (UDA). A UDA is a custom aggregate operator. CREATE DERBY AGGREGATE statement SQL statementsCREATE DERBY AGGREGATE user-defined aggregatescreating aggregatesuser-defined Syntax CREATE DERBY AGGREGATE aggregateName FOR valueDataType [ RETURNS returnDataType ] EXTERNAL NAME singleQuotedString

The aggregate name is composed of an optional schemaName and a SQLIdentifier. If a schemaName is not provided, the current schema is the default schema. If a qualified aggregate name is specified, the schema name cannot begin with SYS.

In general, UDAs live in the same namespace as one-argument user-defined functions (see ). A schema-qualified UDA name may not be the schema-qualified name of a one-argument user-defined function.

An unqualified UDA name (that is, the UDA name without its schema name) may not be the name of an aggregate defined in part 2 of the SQL Standard, section 10.9:

ANY AVG COLLECT COUNT EVERY FUSION INTERSECTION MAX MIN SOME STDDEV_POP STDDEV_SAMP SUM VAR_POP VAR_SAMP

In addition, an unqualified UDA name may not be the name of any of the built-in functions which take one argument.

The valueDataType can be any valid nullable data type except for XML, including user-defined types.

The returnDataType can be any valid nullable data type except for XML. If the returnDataType is omitted, it defaults to be the same as valueDataType.

The singleQuotedString specified by the is the full name of a Java class which implements the org.apache.derby.agg.Aggregator interface. That contract is not checked until a statement is compiled which invokes the UDA.

The org.apache.derby.agg.Aggregator interface extends java.io.Serializable, so you must make sure that all of the state of your UDA is serializable. A UDA may be serialized to disk when it performs grouped aggregation over a large number of groups. That is, intermediate results may be serialized to disk for a query like the following:

SELECT a, myAggregate( b ) FROM myTable GROUP BY a

The serialization will fail if the UDA contains non-serializable fields.

The owner of the schema where the UDA lives automatically gains the USAGE privilege on the UDA and can grant this privilege to other users and roles. Only the database owner and the owner of the UDA can grant these USAGE privileges. The USAGE privilege cannot be revoked from the schema owner. See and for more information.

 Examples CREATE DERBY AGGREGATE mode FOR INT EXTERNAL NAME 'com.example.myapp.aggs.Mode'; CREATE DERBY AGGREGATE types.maxPrice FOR PRICE EXTERNAL NAME 'com.example.myapp.types.PriceMaxer'; CREATE DERBY AGGREGATE types.avgLength FOR VECTOR RETURNS DOUBLE EXTERNAL NAME 'com.example.myapp.types.VectorLength';

See "Programming user-defined aggregates" in the for more details about creating and using user-defined aggregates.