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 | +-------+------+