Previous Next JavaScript must be enabled to correctly display this content

## aggregate

Performs simple data aggregation operations on numeric fields like count, sum, average, keys, start and end. The aggregate command iterates matching keys or rows in the store so, depending on the size of the specified key or row, it may take a very long time to complete.

aggregate table is an aggregate subcommand.

### aggregate table

    aggregate table -name <name>
        [-count] [-sum <field[,field,..]>]
        [-avg <field[,field,..]>]
        [-index <name>]
        [-field <name> -value <value>]*
        [-field <name> [-start <value>] [-end <value>]]
        [-json <string>]

Performs simple data aggregation operations on numeric fields of the table.

where:

- `-name`

  Specifies the table for the operation.

- `-count`

  Returns the count of matching records.

- `-sum`

  Returns the sum of the values of matching fields.

- `-avg`

  Returns the average of the values of matching fields.

- `-index`

  Specifies the name of the index to use. When an index is used, the fields named must belong to the specified index and the aggregation is performed over rows with matching index entries.

- `-field` and `-value` pairs are used to specify the field values of the primary key to use to match for the aggregation, or you can use an empty key to match the entire table.

- The `-field` flat, along with its `-start` and `-end` flags, can be used for restricting the range used to match rows.

- `-json`

  Specifies the fields and values to use for the aggregation as a JSON input string.

See the example below:

    # Create a table 'user_test' with an index on user_test(age):
    kv-> execute 'CREATE TABLE user_test (id INTEGER,
    firstName STRING, lastName STRING, age INTEGER, PRIMARY KEY (id))'
    Statement completed successfully

    kv-> execute 'CREATE INDEX idx1 on user_test (age)'
    Statement completed successfully

    # Insert 3 rows:
    kv-> put table -name user_test -json
    '{"id":1,"firstName":"joe","lastName":"wang","age":21}'
    Operation successful, row inserted.
    kv-> put table -name user_test -json
    '{"id":2,"firstName":"jack","lastName":"zhao","age":32}'
    Operation successful, row inserted.
    kv-> put table -name user_test -json
    '{"id":3,"firstName":"john","lastName":"gu","age":43}'
    Operation successful, row inserted.

    # Get count(*), sum(age) and avg(age) of rows in table:
    kv-> aggregate table -name user_test -count -sum age -avg age
    Row count: 3
    Sum:
            age(3 values): 96
    Average:
            age(3 values): 32.00

    # Get count(*), sum(age) and avg(age) of rows where
    age >= 30, idx1 is utilized to filter the rows:
    kv-> aggregate table -name user_test -count -sum age
    -avg age -index idx1 -field age -start 30
    Row count: 2
    Sum:
            age(2 values): 75
    Average:
            age(2 values): 37.50