Yandex.Cloud
  • Services
  • Why Yandex.Cloud
  • Pricing
  • Documentation
  • Contact us
Get started
Yandex DataLens
  • Getting started
  • Use cases
    • All use cases
    • Visualizing data from a CSV file
    • Visualizing data from a ClickHouse database
    • Visualizing data from Yandex.Metriсa
    • Visualizing data from Yandex.Metrica Logs API
    • Publishing a chart with a map from a CSV file to DataLens Public
    • Visualizing data from AppMetrica
    • Visualizing geodata from a CSV file
  • Step-by-step instructions
    • All instructions
    • Working with connections
      • Creating a ClickHouse connection
      • Creating a connection to a CSV file
      • Creating a MySQL connection
      • Creating a PostgreSQL connection
      • Creating an MS SQL Server connection
      • Creating an Oracle Database connection
      • Creating a Yandex.Metrica API connection
      • Creating a Yandex.Metrica Logs API connection
      • Creating an AppMetrica connection
      • Managing connection access
    • Working with datasets
      • Create dataset
      • Creating a data field
      • Creating a calculated data field
      • Updating fields in datasets
      • Dataset materialization
      • Managing dataset access
      • Managing access to data rows
    • Working with charts
      • Creating a line chart
      • Creating an area chart
      • Creating a pie chart
      • Creating a column chart
      • Creating a bar chart
      • Creating a map
      • Creating a table
      • Creating a pivot table
      • Publishing a chart
      • Managing chart access
    • Working with dashboards
      • Creating dashboards
      • Adding charts to dashboards
      • Adding selectors to dashboards
      • Publishing dashboards
      • Managing dashboard access
    • Working with permissions
      • Granting permissions
      • Deleting permissions
      • Request permissions
  • Concepts
    • Overview
    • Connections
    • Data types
    • Datasets
      • Overview
      • Data model
      • Dataset settings
    • Charts
    • Dashboards
    • Using Markdown in DataLens
    • DataLens Public
    • Calculated fields
    • Marketplace
    • Backups in DataLens
    • Quotas and limits
  • Access management
    • Managing access to DataLens
    • Managing access at the data row level
  • Pricing policy
  • Function reference
    • All Functions
    • Aggregate functions
      • Overview
      • ALL_CONCAT
      • ANY
      • ARG_MAX
      • ARG_MIN
      • AVG
      • AVG_IF
      • COUNT
      • COUNTD
      • COUNTD_APPROX
      • COUNTD_IF
      • COUNT_IF
      • MAX
      • MEDIAN
      • MIN
      • QUANTILE
      • QUANTILE_APPROX
      • STDEV
      • STDEVP
      • SUM
      • SUM_IF
      • TOP_CONCAT
      • VAR
      • VARP
    • Date/Time functions
      • Overview
      • DATEADD
      • DATEPART
      • DATETRUNC
      • DAY
      • DAYOFWEEK
      • HOUR
      • MINUTE
      • MONTH
      • NOW
      • SECOND
      • TODAY
      • WEEK
      • YEAR
    • Geographical functions
      • Overview
      • GEOCODE
      • GEOINFO
      • TOPONYM_TO_GEOPOINT
      • TOPONYM_TO_GEOPOLYGON
    • Logical functions
      • Overview
      • CASE
      • IF
      • IFNULL
      • ISNULL
      • ZN
    • Text markup functions
      • Overview
      • BOLD
      • ITALIC
      • MARKUP
      • URL
    • Mathematical functions
      • Overview
      • ABS
      • ACOS
      • ASIN
      • ATAN
      • ATAN2
      • CEILING
      • COS
      • COT
      • DEGREES
      • DIV
      • EXP
      • FLOOR
      • GREATEST
      • LEAST
      • LN
      • LOG
      • LOG10
      • PI
      • POWER
      • RADIANS
      • ROUND
      • SIGN
      • SIN
      • SQRT
      • SQUARE
      • TAN
    • Operators
      • Overview
      • AND
      • Addition and concatenation (+)
      • BETWEEN
      • Comparison
      • Division (/)
      • IN
      • IS FALSE
      • IS TRUE
      • LIKE
      • Modulo (%)
      • Multiplication (*)
      • NOT
      • Negation (-)
      • OR
      • Power (^)
      • Subtraction (-)
    • String functions
      • Overview
      • ASCII
      • CHAR
      • CONCAT
      • CONTAINS
      • ENDSWITH
      • FIND
      • ICONTAINS
      • IENDSWITH
      • ISTARTSWITH
      • LEFT
      • LEN
      • LOWER
      • LTRIM
      • REGEXP_EXTRACT
      • REGEXP_EXTRACT_NTH
      • REGEXP_MATCH
      • REGEXP_REPLACE
      • REPLACE
      • RIGHT
      • RTRIM
      • SPACE
      • SPLIT
      • STARTSWITH
      • SUBSTR
      • TRIM
      • UPPER
      • UTF8
    • Time series functions
      • Overview
      • AGO
      • AT_DATE
    • Type conversion functions
      • Overview
      • BOOL
      • DATE
      • DATETIME
      • DATETIME_PARSE
      • DATE_PARSE
      • DB_CAST
      • FLOAT
      • GEOPOINT
      • GEOPOLYGON
      • INT
      • STR
    • Window functions
      • Overview
      • AVG
      • AVG_IF
      • COUNT
      • COUNT_IF
      • LAG
      • MAVG
      • MAX
      • MCOUNT
      • MIN
      • MMAX
      • MMIN
      • MSUM
      • RANK
      • RANK_DENSE
      • RANK_PERCENTILE
      • RANK_UNIQUE
      • RAVG
      • RCOUNT
      • RMAX
      • RMIN
      • RSUM
      • SUM
      • SUM_IF
    • Function Availability
  • Questions and answers
  1. Function reference
  2. Aggregate functions
  3. Overview

Aggregate functions

  • Usage Restrictions
  • ALL_CONCAT
  • ANY
  • ARG_MAX
  • ARG_MIN
  • AVG
  • AVG_IF
  • COUNT
  • COUNT_IF
  • COUNTD
  • COUNTD_APPROX
  • COUNTD_IF
  • MAX
  • MEDIAN
  • MIN
  • QUANTILE
  • QUANTILE_APPROX
  • STDEV
  • STDEVP
  • SUM
  • SUM_IF
  • TOP_CONCAT
  • VAR
  • VARP

Aggregate functions (or aggregations) are functions that combine multiple values from a group of entries into one, thus collapsing the group into a single entry.

If you add an aggregation to a dimension, it becomes a measure.

Usage Restrictions

There are the following features of using aggregations:

  1. An aggregate function cannot be nested into another aggregation. The following usage is forbidden: MAX(SUM([Sales])). Any expression can be aggregated only once.
  2. A function or operator cannot have aggregate and non-aggregate expressions as its arguments simultaneously. The following usage is forbidden: CONCAT([Profit], SUM([Profit])).

ALL_CONCAT

Syntax:ALL_CONCAT( expression [ , separator ] )

Returns a string that contains all grouped values of expression delimited by separator (if separator is not specified, a comma is used).

ANY

Syntax:ANY( value )

Returns one of the values of value from the group. This is a nondeterministic aggregation — the result may vary for the same data over multiple queries.

ARG_MAX

Syntax:ARG_MAX( value, comp )

Returns value for the maximum value of comp in the group. If multiple values of value match the maximum value of comp, then the first one encountered is returned. This makes the function non-deterministic.

ARG_MIN

Syntax:ARG_MIN( value, comp )

Returns value for the minimum value of comp in the group. If multiple values of value match the minimum value of comp, then the first one encountered is returned. This makes the function non-deterministic.

AVG

Syntax:AVG( value )

Returns the average of all values. Applicable to numeric data types as well as Date | Datetime.

AVG_IF

Syntax:AVG_IF( expression, condition )

Returns the average of all values that meet the condition condition. If the values don't exist, it returns NULL. Applicable to numeric data types only.

COUNT

Syntax:COUNT( [ value ] )

Returns the number of items in the group.

COUNT_IF

Syntax:COUNT_IF( condition )

Returns the number of items in the group meeting the condition condition.

COUNTD

Syntax:COUNTD( value )

Returns the number of unique values in the group.

See also COUNTD_APPROX.

COUNTD_APPROX

Syntax:COUNTD_APPROX( value )

Returns the approximate number of unique values in the group. Faster than COUNTD, but doesn't guarantee accuracy.

COUNTD_IF

Syntax:COUNTD_IF( expression, condition )

Returns the number of unique values in the group that meet the condition condition.

See also COUNTD_APPROX.

MAX

Syntax:MAX( value )

Returns the maximum value.

If value:

  • number — Returns the largest number.
  • date — Returns the latest date.
  • string — Returns the last value in the alphabetic order.

MEDIAN

Syntax:MEDIAN( value )

Returns the median value.

MIN

Syntax:MIN( value )

Returns the minimum value.

If value:

  • number — Returns the smallest number.
  • date — Returns the earliest date.
  • string — Returns the first value in the alphabetic order.

QUANTILE

Syntax:QUANTILE( value, quant )

Returns the precise quant-level quantile (quant should be in range from 0 to 1).

QUANTILE_APPROX

Syntax:QUANTILE_APPROX( value, quant )

Returns the approximate quant-level quantile (quant should be in range from 0 to 1).

STDEV

Syntax:STDEV( value )

Returns the statistical standard deviation of all values in the expression based on a selection from the population.

STDEVP

Syntax:STDEVP( value )

Returns the statistical standard deviation of all values in the expression based on the biased population.

SUM

Syntax:SUM( value )

Returns the sum of all expression values. Applicable to numeric data types only.

SUM_IF

Syntax:SUM_IF( expression, condition )

Returns the sum of all the expression values that meet the condition condition. Applicable to numeric data types only.

TOP_CONCAT

Syntax:TOP_CONCAT( expression, amount [ , separator ] )

Returns a string that contains top amount grouped values of expression delimited by separator (if separator is not specified, a comma is used).

VAR

Syntax:VAR( value )

Returns the statistical variance of all values in an expression based on a selection from the population.

VARP

Syntax:VARP( value )

Returns the statistical variance of all values in an expression across the entire population.

In this article:
  • Usage Restrictions
  • ALL_CONCAT
  • ANY
  • ARG_MAX
  • ARG_MIN
  • AVG
  • AVG_IF
  • COUNT
  • COUNT_IF
  • COUNTD
  • COUNTD_APPROX
  • COUNTD_IF
  • MAX
  • MEDIAN
  • MIN
  • QUANTILE
  • QUANTILE_APPROX
  • STDEV
  • STDEVP
  • SUM
  • SUM_IF
  • TOP_CONCAT
  • VAR
  • VARP
Language
Careers
Privacy policy
Terms of use
© 2021 Yandex.Cloud LLC