Click Add SELECT columns to select the data for the query. Data selected enters one big bucket, and any functions set for any of the data fields is performed on the entire bucket of data.
Tip: Fields in shown in italics on the Data Options panel are derived, referenced, or side table fields (rather than “hard event data” in the main database tables). See also, Data Fields and Variables.
The Query Structure section at the top provides a summary of the fields selected in the SELECT section at the bottom. If you add GROUP BY or ORDER BY settings, these show up here also.
You can select from Fields and Global Variables, Field Sets, or Local Variables as data to build the query. Choosing a field set limits the fields shown to the selected field set.
Click a field or variable to select it.
Click again to deselect it (remove the checkmark).
To edit a field or variable that you already have set as a query condition (showing under SELECT), simply double-click it or select it (click once) and click the Edit button () in the toolbar. (For example, you might want to edit the query by adding a function to it, as described in SELECT Query Fields.)
To duplicate a field or variable that you already have under SELECT, select it (click once) then click the Duplicate Column button () in the toolbar.
To move column up or down, select it and click the up or down arrow in the toolbar.
You can also select a condition item and right-click to get the various Edit options (Edit, Copy, Delete, Duplicate, and so forth).
Optionally, you can specify an aggregate function on a particular column of data, such as a line item count, or in the case of numeric data, a sum or average
If the query is not grouped by one or more columns, then aggregate functions added here are applied to the whole result set.
If the query is grouped by one or more columns, then the aggregate function is performed on each group individually.
Adding a function adds a data field to the query schema that provides the results of the function, which can later be displayed in a report.
To specify a function for column data, double-click a field or variable in the top pane under SELECT
and select a Function from the drop-down menu to apply to the column data.
The available functions are:
COUNT - Count the number of line items returned in this column.
SUM - Add all numerical data in a column, such as aggregated event count.
AVERAGE - Calculate the average of all numerical data in a column, such as aggregated event count.
MAX - Calculate the top values of the items returned in this column.
MIN - Calculate the lowest values of the items returned in this column.
Standard Deviation (STDDEV) - Calculate the variation from the “average” (mean) for this column. (Square root of the variance.)
VARIANCE - Calculate the amount of variation within the values returned for this column.
Select Unique to apply the function only to unique values in the column. For example, the target address column may have 50 items in it, but only three are unique. To get a count of unique target addresses, check the Unique box.
Click the green checkmark button () to add the function.
To remove a function from a field, select the field, change the function selection to None, and click the green checkmark button again.
To cancel a modification to a function, click the () button or simply click elsewhere on the UI (off of the Function menu.)