HAVING
Use HAVING to filter query results after ShopifyQL calculates metrics. To filter rows before aggregation, use WHERE. A HAVING condition can use returned metrics, aliases, supported function calls, and dimensions that are part of the query result. It can also filter generated result columns when an attribution modifier generates them.
Non-attribution generated result columns aren't valid in HAVING, including total columns, group-total columns, comparison columns, percent-change columns, and cumulative columns. Attribution result columns are valid only when an attribution modifier generates them.
Non-attribution generated result columns aren't valid in HAVING, including total columns, group-total columns, comparison columns, percent-change columns, and cumulative columns. Attribution result columns are valid only when an attribution modifier generates them.
Anchor to ConditionsConditions
A HAVING condition must evaluate to a Boolean value. It can compare compatible returned metrics, aliases, supported function calls, or result dimensions to values. A condition can include literals, parentheses, and the supported operators described in the following sections.
A metric in HAVING must be returned by SHOW, or selected by VISUALIZE when the query doesn't include SHOW. A dimension in HAVING must be part of the query result, such as a dimension returned by SHOW, a dimension grouped by GROUP BY, or the time dimension selected by TIMESERIES. If SHOW renames a returned field with AS, then use the alias in HAVING. For a returned expression that you want to filter, use AS in SHOW, and reference that alias in HAVING.
Anchor to Comparison operatorsComparison operators
Compare compatible result values, aliases, supported function calls, or literals in a HAVING condition. Ordered comparisons require ordered values such as numbers, money, dates, or text.
| Option | Syntax | Description |
|---|---|---|
| equals | <column> = <value> | Matches values equal to the right-hand side. Works with compatible scalar values. |
| not equals | <column> != <value> | Matches values not equal to the right-hand side. Works with compatible scalar values. |
| less than | <column> < <value> | Matches values less than the right-hand side. Use with ordered values such as numbers, money, dates, or text. |
| less than or equal | <column> <= <value> | Matches values less than or equal to the right-hand side. Use with ordered values such as numbers, money, dates, or text. |
| greater than | <column> > <value> | Matches values greater than the right-hand side. Use with ordered values such as numbers, money, dates, or text. |
| greater than or equal | <column> >= <value> | Matches values greater than or equal to the right-hand side. Use with ordered values such as numbers, money, dates, or text. |
Anchor to Range and membership operatorsRange and membership operators
Match a result value against a compatible list, or test whether an ordered value is inside an inclusive range. In HAVING, every referenced field must be available in the query result.
| Option | Syntax | Description |
|---|---|---|
| IN | <column> IN (<value>, ...) | Matches any compatible value in a list. |
| NOT IN | <column> NOT IN (<value>, ...) | Excludes any compatible value in a list. |
| BETWEEN | <column> BETWEEN <low> AND <high> | Matches values within an inclusive range. Use with ordered values such as numbers, money, dates, or text. |
| NOT BETWEEN | <column> NOT BETWEEN <low> AND <high> | Matches values outside an inclusive range. Use with ordered values such as numbers, money, dates, or text. |
Anchor to Null and Boolean checksNull and Boolean checks
Test nullable values or Boolean values in a HAVING condition. Null checks work with any nullable value. Boolean checks require Boolean values.
| Option | Syntax | Description |
|---|---|---|
| IS NULL | IS NULL | Value is null. |
| IS NOT NULL | IS NOT NULL | Value is not null. |
| IS TRUE | IS TRUE | Value is true. |
| IS NOT TRUE | IS NOT TRUE | Value is not true, including false or null. |
| IS FALSE | IS FALSE | Value is false. |
| IS NOT FALSE | IS NOT FALSE | Value is not false, including true or null. |
Anchor to Logical operatorsLogical operators
Combine or negate HAVING conditions. AND is evaluated before OR, so group conditions with parentheses to control the order, for example HAVING a AND (b OR c).
| Option | Syntax | Description |
|---|---|---|
| AND | <condition> AND <condition> | Requires both conditions to match. |
| OR | <condition> OR <condition> | Requires either condition to match. |
| NOT | NOT <condition> | Negates a condition. |
Anchor to String and array matchingString and array matching
Match compatible string values by prefix, suffix, or substring, or check array values for a compatible element. Use matching operators as part of a Boolean HAVING condition.
| Option | Syntax | Description |
|---|---|---|
| STARTS WITH | <column> STARTS WITH <value> | Matches string-like values with the specified prefix. |
| ENDS WITH | <column> ENDS WITH <value> | Matches string-like values with the specified suffix. |
| CONTAINS | <column> CONTAINS <value> | Matches string-like values containing the specified substring, or arrays containing the specified element. |
| NOT CONTAINS | <column> NOT CONTAINS <value> | Matches string-like values or arrays that do not contain the specified value. |
Anchor to Generated result columnsGenerated result columns
Use attribution result columns in HAVING only when an attribution modifier generates them. When a query applies an attribution modifier to a metric, filter the suffixed attribution result column instead of the base metric or its alias.
For attribution column names and modifier details, refer to the WITH attribution result columns section.