---
title: "CREATE AGGREGATE"
url: "https://docs.yugabyte.com/stable/api/ysql/the-sql-language/statements/ddl_create_aggregate/"
---

# CREATE AGGREGATE

Use the CREATE AGGREGATE statement to create an aggregate function.

## Synopsis

Use the CREATE AGGREGATE statement to create an aggregate function. There are three ways to create aggregates.

## Syntax

- [![Grammar Icon](/icons/file-lines.svg)Grammar](#grammar-1)
- [![Diagram Icon](/icons/diagram.svg)Diagram](#diagram-1)

```output.ebnf
create_aggregate ::= create_aggregate_normal
                     | create_aggregate_order_by
                     | create_aggregate_old

create_aggregate_normal ::= CREATE AGGREGATE aggregate_name ( 
                            { aggregate_arg [ , ... ] | * } )  ( SFUNC 
                            = sfunc , STYPE = state_data_type 
                            [ , aggregate_normal_option [ ... ] ] )

create_aggregate_order_by ::= CREATE AGGREGATE aggregate_name ( 
                              [ aggregate_arg [ , ... ] ] ORDER BY 
                              aggregate_arg [ , ... ] )  ( SFUNC = 
                              sfunc , STYPE = state_data_type 
                              [ , aggregate_order_by_option [ ... ] ] 
                              )

create_aggregate_old ::= CREATE AGGREGATE aggregate_name ( BASETYPE = 
                         base_type ,  SFUNC = sfunc , STYPE = 
                         state_data_type 
                         [ , aggregate_old_option [ ... ] ] )

aggregate_arg ::= [ aggregate_arg_mode ] [ formal_arg ] arg_type

aggregate_normal_option ::= SSPACE = state_data_size
                            | FINALFUNC = ffunc
                            | FINALFUNC_EXTRA
                            | FINALFUNC_MODIFY = 
                              { READ_ONLY | SHAREABLE | READ_WRITE }
                            | COMBINEFUNC = combinefunc
                            | SERIALFUNC = serialfunc
                            | DESERIALFUNC = deserialfunc
                            | INITCOND = initial_condition
                            | MSFUNC = msfunc
                            | MINVFUNC = minvfunc
                            | MSTYPE = mstate_data_type
                            | MSSPACE = mstate_data_size
                            | MFINALFUNC = mffunc
                            | MFINALFUNC_EXTRA
                            | MFINALFUNC_MODIFY = 
                              { READ_ONLY | SHAREABLE | READ_WRITE }
                            | MINITCOND = minitial_condition
                            | SORTOP = sort_operator
                            | PARALLEL = 
                              { SAFE | RESTRICTED | UNSAFE }

aggregate_order_by_option ::= SSPACE = state_data_size
                              | FINALFUNC = ffunc
                              | FINALFUNC_EXTRA
                              | FINALFUNC_MODIFY = 
                                { READ_ONLY | SHAREABLE | READ_WRITE }
                              | INITCOND = initial_condition
                              | PARALLEL = 
                                { SAFE | RESTRICTED | UNSAFE }
                              | HYPOTHETICAL

aggregate_old_option ::= SSPACE = state_data_size
                         | FINALFUNC = ffunc
                         | FINALFUNC_EXTRA
                         | FINALFUNC_MODIFY = 
                           { READ_ONLY | SHAREABLE | READ_WRITE }
                         | COMBINEFUNC = combinefunc
                         | SERIALFUNC = serialfunc
                         | DESERIALFUNC = deserialfunc
                         | INITCOND = initial_condition
                         | MSFUNC = msfunc
                         | MINVFUNC = minvfunc
                         | MSTYPE = mstate_data_type
                         | MSSPACE = mstate_data_size
                         | MFINALFUNC = mffunc
                         | MFINALFUNC_EXTRA
                         | MFINALFUNC_MODIFY = 
                           { READ_ONLY | SHAREABLE | READ_WRITE }
                         | MINITCOND = minitial_condition
                         | SORTOP = sort_operator
```

#### create\_aggregate

[create\_aggregate\_normal](#create-aggregate-normal)[create\_aggregate\_order\_by](#create-aggregate-order-by)[create\_aggregate\_old](#create-aggregate-old)

#### create\_aggregate\_normal

CREATEAGGREGATE[aggregate\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#aggregate-name)(,[aggregate\_arg](#aggregate-arg)\*)(SFUNC=[sfunc](/stable/api/ysql/syntax_resources/grammar_diagrams#sfunc),STYPE=[state\_data\_type](/stable/api/ysql/syntax_resources/grammar_diagrams#state-data-type),[aggregate\_normal\_option](#aggregate-normal-option))

#### create\_aggregate\_order\_by

CREATEAGGREGATE[aggregate\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#aggregate-name)(,[aggregate\_arg](#aggregate-arg)ORDERBY,[aggregate\_arg](#aggregate-arg))(SFUNC=[sfunc](/stable/api/ysql/syntax_resources/grammar_diagrams#sfunc),STYPE=[state\_data\_type](/stable/api/ysql/syntax_resources/grammar_diagrams#state-data-type),[aggregate\_order\_by\_option](#aggregate-order-by-option))

#### create\_aggregate\_old

CREATEAGGREGATE[aggregate\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#aggregate-name)(BASETYPE=[base\_type](/stable/api/ysql/syntax_resources/grammar_diagrams#base-type),SFUNC=[sfunc](/stable/api/ysql/syntax_resources/grammar_diagrams#sfunc),STYPE=[state\_data\_type](/stable/api/ysql/syntax_resources/grammar_diagrams#state-data-type),[aggregate\_old\_option](#aggregate-old-option))

#### aggregate\_arg

[aggregate\_arg\_mode](/stable/api/ysql/syntax_resources/grammar_diagrams#aggregate-arg-mode)[formal\_arg](/stable/api/ysql/syntax_resources/grammar_diagrams#formal-arg)[arg\_type](/stable/api/ysql/syntax_resources/grammar_diagrams#arg-type)

#### aggregate\_normal\_option

SSPACE=[state\_data\_size](/stable/api/ysql/syntax_resources/grammar_diagrams#state-data-size)FINALFUNC=[ffunc](/stable/api/ysql/syntax_resources/grammar_diagrams#ffunc)FINALFUNC\_EXTRAFINALFUNC\_MODIFY=READ\_ONLYSHAREABLEREAD\_WRITECOMBINEFUNC=[combinefunc](/stable/api/ysql/syntax_resources/grammar_diagrams#combinefunc)SERIALFUNC=[serialfunc](/stable/api/ysql/syntax_resources/grammar_diagrams#serialfunc)DESERIALFUNC=[deserialfunc](/stable/api/ysql/syntax_resources/grammar_diagrams#deserialfunc)INITCOND=[initial\_condition](/stable/api/ysql/syntax_resources/grammar_diagrams#initial-condition)MSFUNC=[msfunc](/stable/api/ysql/syntax_resources/grammar_diagrams#msfunc)MINVFUNC=[minvfunc](/stable/api/ysql/syntax_resources/grammar_diagrams#minvfunc)MSTYPE=[mstate\_data\_type](/stable/api/ysql/syntax_resources/grammar_diagrams#mstate-data-type)MSSPACE=[mstate\_data\_size](/stable/api/ysql/syntax_resources/grammar_diagrams#mstate-data-size)MFINALFUNC=[mffunc](/stable/api/ysql/syntax_resources/grammar_diagrams#mffunc)MFINALFUNC\_EXTRAMFINALFUNC\_MODIFY=READ\_ONLYSHAREABLEREAD\_WRITEMINITCOND=[minitial\_condition](/stable/api/ysql/syntax_resources/grammar_diagrams#minitial-condition)SORTOP=[sort\_operator](/stable/api/ysql/syntax_resources/grammar_diagrams#sort-operator)PARALLEL=SAFERESTRICTEDUNSAFE

#### aggregate\_order\_by\_option

SSPACE=[state\_data\_size](/stable/api/ysql/syntax_resources/grammar_diagrams#state-data-size)FINALFUNC=[ffunc](/stable/api/ysql/syntax_resources/grammar_diagrams#ffunc)FINALFUNC\_EXTRAFINALFUNC\_MODIFY=READ\_ONLYSHAREABLEREAD\_WRITEINITCOND=[initial\_condition](/stable/api/ysql/syntax_resources/grammar_diagrams#initial-condition)PARALLEL=SAFERESTRICTEDUNSAFEHYPOTHETICAL

#### aggregate\_old\_option

SSPACE=[state\_data\_size](/stable/api/ysql/syntax_resources/grammar_diagrams#state-data-size)FINALFUNC=[ffunc](/stable/api/ysql/syntax_resources/grammar_diagrams#ffunc)FINALFUNC\_EXTRAFINALFUNC\_MODIFY=READ\_ONLYSHAREABLEREAD\_WRITECOMBINEFUNC=[combinefunc](/stable/api/ysql/syntax_resources/grammar_diagrams#combinefunc)SERIALFUNC=[serialfunc](/stable/api/ysql/syntax_resources/grammar_diagrams#serialfunc)DESERIALFUNC=[deserialfunc](/stable/api/ysql/syntax_resources/grammar_diagrams#deserialfunc)INITCOND=[initial\_condition](/stable/api/ysql/syntax_resources/grammar_diagrams#initial-condition)MSFUNC=[msfunc](/stable/api/ysql/syntax_resources/grammar_diagrams#msfunc)MINVFUNC=[minvfunc](/stable/api/ysql/syntax_resources/grammar_diagrams#minvfunc)MSTYPE=[mstate\_data\_type](/stable/api/ysql/syntax_resources/grammar_diagrams#mstate-data-type)MSSPACE=[mstate\_data\_size](/stable/api/ysql/syntax_resources/grammar_diagrams#mstate-data-size)MFINALFUNC=[mffunc](/stable/api/ysql/syntax_resources/grammar_diagrams#mffunc)MFINALFUNC\_EXTRAMFINALFUNC\_MODIFY=READ\_ONLYSHAREABLEREAD\_WRITEMINITCOND=[minitial\_condition](/stable/api/ysql/syntax_resources/grammar_diagrams#minitial-condition)SORTOP=[sort\_operator](/stable/api/ysql/syntax_resources/grammar_diagrams#sort-operator)

## Semantics

The order of options does not matter. Even the mandatory options BASETYPE, SFUNC, and STYPE may appear in any order.

See the semantics of each option in the [PostgreSQL docs](https://www.postgresql.org/docs/15/sql-createaggregate.html "PostgreSQL docs").

## Examples

Normal syntax example.

```plpgsql
yugabyte=# CREATE AGGREGATE sumdouble (float8) (
              STYPE = float8,
              SFUNC = float8pl,
              MSTYPE = float8,
              MSFUNC = float8pl,
              MINVFUNC = float8mi
           );
yugabyte=# CREATE TABLE normal_table(
             f float8,
             i int
           );
yugabyte=# INSERT INTO normal_table(f, i) VALUES
             (0.1, 9),
             (0.9, 1);
yugabyte=# SELECT sumdouble(f), sumdouble(i) FROM normal_table;
```

Order by syntax example.

```plpgsql
yugabyte=# CREATE AGGREGATE my_percentile_disc(float8 ORDER BY anyelement) (
             STYPE = internal,
             SFUNC = ordered_set_transition,
             FINALFUNC = percentile_disc_final,
             FINALFUNC_EXTRA = true,
             FINALFUNC_MODIFY = read_write
           );
yugabyte=# SELECT my_percentile_disc(0.1), my_percentile_disc(0.9)
             WITHIN GROUP (ORDER BY typlen)
             FROM pg_type;
```

Old syntax example.

```plpgsql
yugabyte=# CREATE AGGREGATE oldcnt(
             SFUNC = int8inc,
             BASETYPE = 'ANY',
             STYPE = int8,
             INITCOND = '0'
           );
yugabyte=# SELECT oldcnt(*) FROM pg_aggregate;
```

Zero-argument aggregate example.

```plpgsql
yugabyte=# CREATE AGGREGATE newcnt(*) (
             SFUNC = int8inc,
             STYPE = int8,
             INITCOND = '0',
             PARALLEL = SAFE
           );
yugabyte=# SELECT newcnt(*) FROM pg_aggregate;
```

## See also

- [ALTER AGGREGATE](/stable/api/ysql/the-sql-language/statements/ddl_alter_aggregate "ALTER AGGREGATE")
- [DROP AGGREGATE](/stable/api/ysql/the-sql-language/statements/ddl_drop_aggregate "DROP AGGREGATE")