# CREATE DERBY AGGREGATE statement

The CREATE DERBY AGGREGATE statement creates a user-defined aggregate (UDA). A UDA is a custom aggregate operator.

## 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 CREATE FUNCTION statement). 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 Derby built-in functions which take one argument.

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

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

The singleQuotedString specified by the EXTERNAL NAME clause 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 GRANT statement and REVOKE statement 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 Derby Developer's Guide for more details about creating and using user-defined aggregates.

Parent topic: CREATE statements
Related reference
CREATE FUNCTION statement
CREATE INDEX statement
CREATE PROCEDURE statement
CREATE ROLE statement
CREATE SCHEMA statement
CREATE SEQUENCE statement
CREATE SYNONYM statement
CREATE TABLE statement
CREATE TRIGGER statement
CREATE TYPE statement
CREATE VIEW statement