Get started with Atlassian Analytics
Learn how to add Atlassian Analytics to a site and understand what you need to query data and create charts.
Group and aggregate functions
When you change the grouping or aggregation of a column via its aggregation menu or use a “Group & aggregate” step, you’re presented with different function options depending on the column’s data type.
Note that only some of these functions are available when using the “Group and aggregate” step in Visual SQL.
Group functions
For string data types
Operator | Description |
|---|---|
Group | Lists all unique values |
Count of unique | Number of unique 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 |
Minute | Buckets the time by minutes; only available for datetimes |
Hour | Buckets the time by hours; only available for datetimes |
Day | Buckets the time by days |
Week | Buckets the time by weeks |
Month | Buckets the time by months |
Quarter | Buckets the time by quarters |
Year | Buckets the time by years |
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 unique | Number of unique 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 unique datetime values |
Unaggregated | Lists all datetime values, including duplicates |
For boolean data types
Operator | Description |
|---|---|
Group | Lists all unique values |
Count of unique | Number of unique 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 unique rows of the array column |
Explode | Lists the unique values of the arrays into separate rows |
Concatenate | 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 functions
Aggregate functions are available for columns that have a data type of number.
Operator | Description |
|---|---|
Count of unique | Number of unique values |
Approximate count unique | An approximation for the number of unique values; less memory-intensive than "Count of unqiue" 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 unique values |
Unaggregated | Lists all values, including duplicates |
Was this helpful?