Reference
ResourcesCommunityStatusLogin

Mathematical Aggregations

AVG

AVG(x) Returns an average of all the elements in x. All elements are implicitly promoted to float. Return value is of type float.

WITH examples AS (
      SELECT 3 AS x
      UNION ALL SELECT 5
      UNION ALL SELECT 10
  )
  SELECT AVG(x)
  FROM examples
6.0
WITH examples AS (
      SELECT 3 AS x, true AS y
      UNION ALL SELECT 5, false
      UNION ALL SELECT 10, true
      UNION ALL SELECT 1, false
  )
  SELECT y, AVG(x) avg
  FROM examples
  GROUP BY y
+-------+------+ | y | avg | |-------+------| | True | 6.5 | | False | 3.0 | +-------+------+

MAX

MAX(x) Returns the maximum value of all elements in x.

WITH examples AS (
      SELECT 3 AS x
      UNION ALL SELECT null
      UNION ALL SELECT 10
  )
  SELECT MAX(x)
  FROM examples
10
WITH examples AS (
      SELECT 3 AS x, true AS y
      UNION ALL SELECT 5, false
      UNION ALL SELECT 10, true
      UNION ALL SELECT 1, false
  )
  SELECT y, MAX(x) max
  FROM examples
  GROUP BY y
+-------+------+ | y | max | |-------+------| | True | 10 | | False | 5 | +-------+------+

MAX_BY

MAX_BY(x, y) Returns the value of column x associated with the maximum value of column y.

WITH examples AS (
      SELECT 3 AS x, 5 AS y
      UNION ALL SELECT 1, 10
      UNION ALL SELECT 10, 2
  )
  SELECT MAX_BY(x, y)
  FROM examples
1

MIN

MIN(x) Returns the minimum value of all elements in x.

WITH examples AS (
      SELECT 3 AS x
      UNION ALL SELECT null
      UNION ALL SELECT 10
  )
  SELECT MIN(x)
  FROM examples
3
WITH examples AS (
      SELECT 3 AS x, true AS y
      UNION ALL SELECT 5, false
      UNION ALL SELECT 10, true
      UNION ALL SELECT 1, false
  )
  SELECT y, MIN(x) min
  FROM examples
  GROUP BY y
+-------+------+ | y | min | |-------+------| | True | 3 | | False | 1 | +-------+------+

MIN_BY

MIN_BY(x, y) Returns the value of column x associated with the minimum value of column y.

WITH examples AS (
      SELECT 3 AS x, 5 AS y
      UNION ALL SELECT 1, 10
      UNION ALL SELECT 10, 2
  )
  SELECT MIN_BY(x, y)
  FROM examples
10

STDDEV_SAMP

STDDEV_SAMP(x) Returns the sample standard deviation of all input values.

WITH examples AS (
      SELECT 3 AS x
      UNION ALL SELECT null -- ignored in STDEV calculation
      UNION ALL SELECT 10
  )
  SELECT STDDEV_SAMP(x)
  FROM examples
4.949747468305833

SUM

SUM(x) Returns the sum of all elements in x. Returns a value of type int if all of the input elements are int, float otherwise.

WITH examples AS (
      SELECT 3 AS x
      UNION ALL SELECT null -- ignored in SUM calculation
      UNION ALL SELECT 10
  )
  SELECT SUM(x)
  FROM examples
13
WITH examples AS (
      SELECT 3 AS x, true AS y
      UNION ALL SELECT 5, false
      UNION ALL SELECT 10, true
      UNION ALL SELECT 1, false
  )
  SELECT y, SUM(x) sum
  FROM examples
  GROUP BY y
+-------+------+ | y | sum | |-------+------| | True | 13 | | False | 6 | +-------+------+