Aggregations are operations that can be performed on table data that has already been grouped by a time interval. Aggregate functions perform a calculation on a set of values and return a single value. Operations include counting records in a group, identifying the minimum or maximum value in a group, or calculating the sum of field values in a group. When you create an aggregation, a new column appears in the table displaying the results of the operation.
Having already grouped your table data by a selected time interval, follow these steps to aggregate the grouped values:
- Select the Aggregate icon on the query window toolbar. The Operations Over Columns window appears with the Aggregate function option selected.
The Column Name is generated automatically based on the aggregation and arguments you choose. However, you can edit this value if you prefer. Select the Aggregation drop-down list to select the type of aggregation you will perform on the selected argument. To get more information about an aggregation type, click the info icon. Click New Argument to select the arguments on which you want to perform the aggregation.
If you are building your query in the Query editor and the name of your column starts with a number or symbol, you must scape it between `` like in this example:
This is automatically done if your using the interface, so you don't need to enter the symbols.
- When you're done, select Aggregate function to add the column containing the aggregated values to the table.
In the example below, we have analyzed the correlation between status code and bytes transferred in the
demo.ecommerce.data table. First, we have grouped our data with no temporal option by status code and bytes transferred to obtain the unique value combinations found between them. Then, we used the Count aggregation function with no arguments to know the number of occurrences for each combination.
You can use the following query to replicate the example:
from demo.ecommerce.data group by statusCode, bytesTransferred every - select count() as count