Set HAVING Conditions
You can set a Having condition that defines conditions for displaying groups in a query.
Notes:
-
You must add a Group By clause for this option to be available.
- If you reverse engineer a query that contains a Having condition from the Editor or edit it in the Query tab of the Query Builder, it displays in a Global Having Clause on the Diagram pane rather than the Having Condition field below the Diagram pane. You can double-click the Global Having clause, or can click
to edit it
- This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To set a Having condition
-
Select a column from a table in the Diagram tab.
-
Select the Having Condition field below the column and click
.
-
Complete the fields as necessary. Review the following for additional information:
Form Tab
Description
Field
Select an operator or predicate to use in this condition.
Aggregate
Select one of the following functions to calculate column values:
-
Avg—Returns the average of the values in a group.
-
Avg (Distinct)—Returns the average of the unique values in a group.
-
Count—Returns the total number of items in a column. This function does not ignore NULL values when calculating results.
-
Count (Distinct)—Returns the total number of unique values in a column.
-
Max—Returns the maximum value for the column.
-
Min—Returns the minimum value for the column.
-
Stddev—Returns the sample standard deviation of a numeric expression evaluated over a set.
-
Sum—Returns the sum of all the values in the expression.
-
Sum (Distinct)—Returns the sum of all the unique values in the expression.
- Variance—Returns a statistical measure of how far a set of numbers are spread out from each other.
Note: If you add a Group By clause, you must specify an aggregate function for any remaining columns.
Value 1 Type
Value 2 Type
Select whether the expression is a column or constant. After selecting this option, select the column name or enter a constant in the field.
Note: The Value 2 Type field is only available if you selected the Between predicate.
Formula Tab
Description
Functions
Perform an incremental search or select a function, and then select a column on which to perform the function.
Operators
Select an operator or predicate to use in this condition.
Columns and Calculated Fields
Select a column to apply a function or operator against.
-
Tip: To remove a Having condition, open the Having condition window and click Remove.