• Products
  • Documentation
  • Resources

Atlassian Analytics is in an early access program

Only people who are part of this invite-only early access program have access to Atlassian Analytics.

Group and aggregate functions

When you change the grouping or aggregation of a column via its aggregation menu, you’re presented with different operator options depending on the column’s data type.

Group operators

For string data types

Operator

Description

Group

Lists all distinct values

Count of distinct

Number of distinct values

Count of all

Number of all values, including duplicates

Minimum

Sorts the values in alphabetical order and returns the first value

Maximum

Sorts the values in alphabetical order and returns the last value

Unaggregated

Lists all values, including duplicates

For date or datetime data types

Operator

Description

Second

Buckets the time by seconds; only available for datetimes

Format: YYYY-mm-ddTHH:MM:SS

For example, 2020-06-30T03:20:59

Minute

Buckets the time by minutes; only available for datetimes

Format: YYYY-mm-ddTHH:MM

For example, 2020-06-30T03:20

Hour

Buckets the time by hours; only available for datetimes

Format: YYYY-mm-ddTHH

For example, 2020-06-30T03

Day

Buckets the time by days

Format: YYYY-mm-dd

For example, 2020-06-30T03:20

Week

Buckets the time by weeks

Format: YYYY-W(01-52)

For example, 2020-W22

Month

Buckets the time by months

Format: YYYY-mm

For example, 2020-06

Quarter

Buckets the time by quarters

Format: YYYY-Q(1-4)

For example, 2020-Q2

Year

Buckets the time by years

Format: YYYY

For example, 2020

Hour of Day

00 to 24; only available for datetimes

Day of Week

Sunday, Monday, Tuesday, …, or Saturday

Day of Month

01 to 31

Day of Year

001 to 366

Month of Year

January, February, March, …, December

Count of distinct

Number of distinct datetime values

Count of all

Number of all datetime values, including duplicates

Minimum

The earliest datetime value

Maximum

The latest datetime value

Group

Lists all distinct datetime values

Unaggregated

Lists all datetime values, including duplicates

For boolean data types

Operator

Description

Group

Lists all distinct values

Count of distinct

Number of distinct values

Count of all

Number of all values, including duplicates

Unaggregated

Lists all values, including duplicates

For array data types

Operator

Description

Group

Lists all distinct rows of the array column

Explode

Lists the distinct values of the arrays into separate rows

Concat

Concatenates the values of the array into a string with a space between each value

Unaggregated

Lists all rows of the array column

Unaggregated explode

Lists all values of each array into separate rows

Aggregate operators

Aggregate operators are available for columns that have a data type of number.

Operator

Description

Count of distinct

Number of distinct values

Approx count distinct

An approximation for the number of distinct values; less memory-intensive than "Count of distinct" so it's better for very large data sets

Count of all

Number of all values, including duplicates

Total sum

Sums all values

Average

Takes average of all values

Median

The middle number in a sorted list of numbers

Minimum

The smallest value

Maximum

The largest value

Standard deviation

How close the numbers are to the mean; the square root of the variance

Snowflake uses a sample population to calculate the standard deviation.

Variance

The average of the squared differences of the mean

Group

Lists all distinct values

Unaggregated

Lists all values, including duplicates

Additional Help