Aggregate Functions

Table of Contents



Aggregate functions compute one value from the values of all of the documents in a group, as defined by a Group By clause. Without GROUP BY, all of the selected documents form a single group.
  • Avg - Returns the average (arithmetic mean) of the values in each group.
  • Count - Returns the number of values in each group. Count(0) and Count(*) count the documents in each group.
  • CountDistinct - Returns the number of distinct values in each group. Values are compared without regard to case unless caseSensitive is true.
  • GeometricMean - Returns the geometric mean of the values in each group: the nth root of their product. Useful for growth rates and ratios.
  • Max - Returns the largest numeric value in each group. Use MaxString for text.
  • MaxString - Returns the alphabetically largest value in each group.
  • Mean - Returns the arithmetic mean of the values in each group (the same as Avg).
  • Median - Returns the median (middle) value in each group. For an even number of values it is the average of the two middle values.
  • Min - Returns the smallest numeric value in each group. Use MinString for text.
  • MinString - Returns the alphabetically smallest value in each group.
  • Mode - Returns the value that occurs most often in each group. When several values are tied, the first one encountered is returned.
  • Sha1Agg - Returns a single SHA-1 hash of all of the values in each group, as a lower case hex string. Useful for detecting changes to a set of documents.
  • Sha256Agg - Returns a single SHA-256 hash of all of the values in each group, as a lower case hex string. Useful for detecting changes to a set of documents.
  • Sha512Agg - Returns a single SHA-512 hash of all of the values in each group, as a lower case hex string. Useful for detecting changes to a set of documents.
  • Sum - Returns the total of the values in each group.
  • Variance - Returns the population variance of the values in each group: how far the values are spread from their mean.




Home
The home page
SQL :: Avg
Returns the average (arithmetic mean) of the values in each group.
SQL :: Count
Returns the number of values in each group. Count(0) and Count(*) count the documents in each group.
SQL :: CountDistinct
Returns the number of distinct values in each group. Values are compared without regard to case unless caseSensitive is true.
SQL :: Distinct
Removes duplicate rows from the results of a SELECT.
SQL :: Expressions
Literals, operators, comments and variables in KBSQL.
SQL :: GeometricMean
Returns the geometric mean of the values in each group: the nth root of their product. Useful for growth rates and ratios.
SQL :: Group By
Group by collapses a result set into one row per distinct set of values.
SQL :: Keywords
A breakdown of all of the statements and keywords supported by the query processor.
SQL :: Max
Returns the largest numeric value in each group. Use MaxString for text.