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.
AVG(DISTINCT expression
)
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. |
MAX(expression
)
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";
[
{
"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";
[
{
"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";
[
{
"MaxName": "YOSEMITE LODGE AT THE FALLS"
}
]
MIN(expression
)
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";
[
{
"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";
[
{
"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";
[
{
"MinName": "AIRE NATURELLE LE GROZEAU Aire naturelle"
}
]
SUM(expression
)
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`;
[
{
"SumOfStops": 6 (1)
}
]
1 | Because there are 6 routes * 1 stop each = 6 |
Related Links
GROUP BY Clause for GROUP BY, LETTING, and HAVING clauses.