A newer version of this documentation is available.

# Aggregate Functions

+

Aggregate functions take multiple values from documents, perform calculations, and return a single value as the result. The function names are case insensitive.

You can only use aggregate functions in `SELECT`, `LETTING`, `HAVING`, and `ORDER BY` clauses. When using an aggregate function in a query, the query operates as an aggregate query.

Aggregate functions take one argument, which is used to compute the aggregate function. The `COUNT` function can also take a wildcard (*) or a path with a wildcard (path.*) as its argument.

If there is no input row for the group, `COUNT` functions return `0`. All other aggregate functions return NULL.

## ARRAY_AGG(`expression`)

### Return Value

Returns array of the non-MISSING values in the group, including NULL values.

### Examples

List all values of the `Cleanliness` reviews given.

``SELECT ARRAY_AGG(reviews.ratings.Cleanliness) AS Reviews FROM `travel-sample`;``
Results
``````[
{
"Reviews": [
-1,
-1,
-1,
-1,
-1,
...``````

## ARRAY_AGG(DISTINCT `expression`)

### Return Value

Returns array of the distinct non-MISSING values in the group, including NULL values.

### Examples

List all unique values of the `Cleanliness` reviews given.

``SELECT ARRAY_AGG(DISTINCT reviews.ratings.Cleanliness) AS Reviews FROM `travel-sample`;``
Results
``````[
{
"UniqueReviews": [
-1,
1,
2,
3,
4,
5
]
}
]``````

## AVG(`expression`)

### Return Value

Returns arithmetic mean (average) of all the number values in the group.

### Examples

The average altitude of airports in the `travel-sample` keyspace.

``SELECT AVG(geo.alt) AS AverageAltitude FROM `travel-sample` WHERE type="airport";``
Results
``````[
{
"AverageAltitude": 870.1651422764228
}
]``````

## AVG(DISTINCT `expression`)

### Return Value

Returns arithmetic mean (average) of all the distinct number values in the group.

### Examples

The average number of airline route stops vs. the `DISTINCT` average of airline route stops.

``````SELECT AVG(stops) FROM `travel-sample`; (1)

SELECT AVG(DISTINCT stops) FROM `travel-sample`; (2)``````
 1 Results in 0.0002 since nearly all docs have 0 stops. 2 Results in 0.5 since the docs contain only 1 or 0 stops.

## COUNT(*)

### Return Value

Returns count of all the input rows for the group, regardless of value.

### Examples

The number of documents in `travel-sample`.

``SELECT COUNT(*) AS CountAll FROM `travel-sample`;``
Results
``````[
{
"CountAll": 31591
}
]``````

## COUNT(`expression`)

### Return Value

Returns count of all the non-NULL and non-MISSING values in the group.

### Examples

The number of documents with an airline route stop in `travel-sample` regardless of its value.

``SELECT COUNT(stops) AS CountOfStops FROM `travel-sample`;``
Results
``````[
{
"CountOfStops": 24024
}
]``````

## COUNT(DISTINCT `expression`)

### Return Value

Returns count of all the distinct non-NULL and non-MISSING values in the group.

### Examples

The number of unique values of airline route stops in `travel-sample`.

``SELECT COUNT(DISTINCT stops) AS CountOfDistinctStops FROM `travel-sample`;``
Results
``````[
{
"CountOfSDistinctStops": 2 (1)
}
]``````
 1 Because there are only 0 or 1 stops.

## COUNTN(`expression`)

### Return Value

Returns a count of all the numeric values in the group.

### Examples

The count of numeric values in a mixed group.

``````SELECT COUNTN(list.val) AS CountOfNumbers
FROM [
{"val":1},
{"val":1},
{"val":2},
{"val":"abc"}
] AS list;``````
Results
``````[
{
"CountOfNumbers": 3
}
]``````

## COUNTN(DISTINCT `expression` )

### Return Value

Returns a count of all the distinct numeric values in the group.

### Examples

The count of unique numeric values in a mixed group.

``````SELECT COUNTN(DISTINCT list.val) AS CountOfNumbers
FROM [
{"val":1},
{"val":1},
{"val":2},
{"val":"abc"}
] AS list;``````
Results
``````[
{
"CountOfNumbers": 2
}
]``````

## MAX(`expression`)

### Return Value

Returns the maximum non-NULL, non-MISSING value in the group in N1QL collation order.

### Examples

Max of an integer field – Find the northernmost latitude of any hotel in `travel-sample`.

``SELECT MAX(geo.lat) AS MaxLatitude FROM `travel-sample` WHERE type="hotel";``
Results
``````[
{
"MaxLatitude": 60.15356
}
]``````

Max of a string field – Find the hotel whose name is last alphabetically in `travel-sample`.

``SELECT MAX(name) AS MaxName FROM `travel-sample` WHERE type="hotel";``
Results
``````[
{
"MaxName": "pentahotel Birmingham"
}
]``````

That result might have been surprising since lowercase letters come after uppercase letters and are therefore "higher" than uppercase letters. To avoid this uppercase/lowercase confusion, you should first make all values uppercase or lowercase, as in the following example.

Max of a string field, regardless of case – Find the hotel whose name is last alphabetically in `travel-sample`.

``SELECT MAX(UPPER(name)) AS MaxName FROM `travel-sample` WHERE type="hotel";``
Results
``````[
{
"MaxName": "YOSEMITE LODGE AT THE FALLS"
}
]``````

## MIN(`expression`)

### Return Value

Returns the minimum non-NULL, non-MISSING value in the group in N1QL collation order.

### Examples

Min of an integer field – Find the southernmost latitude of any hotel in `travel-sample`.

``SELECT MIN(geo.lat) AS MinLatitude FROM `travel-sample` WHERE type="hotel";``
Results
``````[
{
"MinLatitude": 32.68092
}
]``````

Min of a string field – Find the hotel whose name is first alphabetically in `travel-sample`.

``SELECT MIN(name) AS MinName FROM `travel-sample` WHERE type="hotel";``
Results
``````[
{
"MinName": "'La Mirande Hotel"
}
]``````

That result might have been surprising since some symbols come before letters and are therefore "lower" than letters. To avoid this symbol confusion, you can specify letters only, as in the following example.

Min of a string field, regardless of preceding non-letters – Find the first hotel alphabetically in `travel-sample`.

``SELECT MIN(name) AS MinName FROM `travel-sample` WHERE type="hotel" AND SUBSTR(name,0)>="A";``
Results
``````[
{
"MinName": "AIRE NATURELLE LE GROZEAU Aire naturelle"
}
]``````

## SUM(`expression`)

### Return Value

Returns sum of all the number values in the group.

### Examples

The sum total of all airline route stops in `travel-sample`.

 In the travel-sample bucket, nearly all flights are non-stop (0 stops) and only six flights have 1 stop, so we expect 6 flights of 1 stop each, a total of 6.
``SELECT SUM(stops) AS SumOfStops FROM `travel-sample`;``
Results
``````[
{
"SumOfStops": 6 (1)
}
]``````
 1 Because there are 6 routes * 1 stop each = 6

## SUM(DISTINCT `expression`)

### Return Value

Returns arithmetic sum of all the distinct number values in the group.

### Examples

The sum total of all unique numbers of airline route stops in `travel-sample`.

``SELECT SUM(DISTINCT stops) AS SumOfDistinctStops FROM `travel-sample`;``
Results
``````[
{
"SumOfDistinctStops": 1 (1)
}
]``````
 1 Because there are only 0 and 1 stops per route; and 0 + 1 = 1

GROUP BY Clause for GROUP BY, LETTING, and HAVING clauses.

Output
{"ft_lang_label":"__label__en","ft_lang_prob":0.7151842,"math_prob":0.91569495,"size":3491,"snap":"2021-43-2021-49","text_gpt3_token_len":858,"char_repetition_ratio":0.14281617,"word_repetition_ratio":0.16420664,"special_character_ratio":0.24319679,"punctuation_ratio":0.111292966,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9728995,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2021-10-21T20:47:03Z\",\"WARC-Record-ID\":\"<urn:uuid:ec348752-ee80-4599-abee-23ca7f1c2cfb>\",\"Content-Length\":\"37126\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:700c2e5f-0032-4149-b34b-225aceeaedde>\",\"WARC-Concurrent-To\":\"<urn:uuid:ed7512f9-353f-498f-82d5-96653bb944fa>\",\"WARC-IP-Address\":\"99.84.191.9\",\"WARC-Target-URI\":\"https://docs.couchbase.com/server/5.5/n1ql/n1ql-language-reference/aggregatefun.html\",\"WARC-Payload-Digest\":\"sha1:QQQ6LHGQGDMHUMNLXLMQ4NMMNKS5U25F\",\"WARC-Block-Digest\":\"sha1:IFV5NWKPFTS26DFBIZSTE2SWRHYKVA2N\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2021/CC-MAIN-2021-43/CC-MAIN-2021-43_segments_1634323585441.99_warc_CC-MAIN-20211021195527-20211021225527-00669.warc.gz\"}"}