SELECT Query Fields

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.

Query Structure (SELECT)

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).

Applying Functions to SELECT Columns

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.

  Note: The Count function is a simple count of all events. It takes into consideration the aggregated event count and counts each event in an aggregated event individually. For example, if an event has an aggregated event count of 5, the Count function counts this event as equivalent to 5 events (with an aggregated event count of 1 each). Take this into account when comparing the number of rows in a report with the “grand total” count based on the Count function.

• 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.

• GROUP_CONCAT - Create a comma-separated list of the aggregated items.

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.)