To enable the use of GROUP BY and aggregate functions in your query, right-click the gray background in the top half of the screen, and then select Group By from the right-click menu.
This adds a Group By column to the grid on the lower half of the screen.
To set the options for each cell in the Group By column, click the cell and then choose an option from the drop-down menu.
Options available in the Group By column include clause identifiers and aggregate functions.
The clause identifiers include:
Group By
Use Group By to define groups of information on which to perform calculations using one of the aggregate functions (see below). For sample queries using Group By, click here.
Expression
When using a complex aggregate function (see below) to calculate a value in a Column, the calculated value should be designated as an Expression. For a sample query using Expression, click here.
Where
To specify criteria for a field that isn't used to define groupings, select the Where option. By default, this hides the field from the query result set. For a sample query using Where, click here.
The aggregate functions include:
Sum and Sum Distinct
For each group defined by Group By, the Sum function calculates the sum of the values you specify, and the Sum Distinct function calculates the sum of only the unique values that you specify. For most connections, the specified values must be numbers.
If no groups have been defined, one overall sum is calculated for the field or values you specify.
For sample queries using Sum, click here.
Avg and Avg Distinct
For each group defined by Group By, the Avg function calculates the average of the values you specify, and the Avg Distinct function calculates the average of only the unique values that you specify. For most connections, the specified values must be numbers.
If no groups have been defined, one overall average is calculated for the field or values you specify.
For sample queries using Avg, click here.
Min and Min Distinct, Max and Max Distinct
For each group defined by Group By, the Min function calculates the minimum of the values you specify, and the Min Distinct function calculates the minimum of only the unique values that you specify. For most connections, the specified values must be numbers or date/time values.
For each group defined by Group By, the Max function calculates the maximum of the values you specify, and the Max Distinct function calculates the maximum of only the unique values that you specify. For most connections, the specified values must be numbers or date/time values.
If no groups have been defined, one overall minimum or maximum is calculated for the field or values you specify.
For sample queries using Min and Max, click here.
Count and Count Distinct
For each group defined by Group By, the Count function counts the number of items that you specify, and the Count Distinct function counts only the number of unique items that you specify. For most connections, the specified values can be text and characters, numbers, date/time values, and Yes/No values.
If no groups have been defined, one overall count is calculated for the values you specify.
For sample queries using Count, click here.
StDev and StDevP
For each group defined by Group By, the StDev function estimates the standard deviation of the values you specify when evaluated as a population sample, and the StDevP function estimates the standard deviation of the values you specify when evaluated as a population. For most connections, the specified values must be numbers.
If no groups have been defined, one overall standard deviation is estimated for the values you specify.
For sample queries using StDev, click here.
Var and VarP
For each group defined by Group By, the Var function estimates the variance of the values you specify when evaluated as a population sample, and the VarP function estimates the variance of the values you specify when evaluated as a population. For most connections, the specified values must be numbers.
If no groups have been defined, one overall variance is estimated for the values you specify.
For sample queries using Var, click here.
More:
Hiding a column from the query results
Setting an alias for the column