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.
Generated result columns aren't valid in HAVING, including total columns, group-total columns, comparison columns, percent-change columns, and cumulative columns.
Generated result columns aren't valid in HAVING, including total columns, group-total columns, comparison columns, percent-change columns, and cumulative columns.
Syntax
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.
ShopifyQL
Examples
Description
Show the 10 products with the highest [`net_sales`](/docs/api/shopifyql/2026-01/schemas/sales_revenue/sales#salesmetric-propertydetail-netsales) last month, limited to those above $1,000 in net sales. This example uses `HAVING` to filter on an aggregated metric after [`GROUP BY`](/docs/api/shopifyql/2026-01/syntax/group-by), which [`WHERE`](/docs/api/shopifyql/2026-01/syntax/where) can't do.
ShopifyQL
FROM sales SHOW net_sales GROUP BY product_title HAVING net_sales > 1000 DURING last_month ORDER BY net_sales DESC LIMIT 10Description
Track daily [`sessions`](/docs/api/shopifyql/2026-01/schemas/sessions_and_behavior/sessions#sessionsmetric-propertydetail-sessions) over the last 30 days, keeping only the days that clear 100. This example shows how `HAVING` tests each day's aggregated bucket in a [`TIMESERIES`](/docs/api/shopifyql/2026-01/syntax/timeseries) query, after the rows are grouped by day.
ShopifyQL
FROM sessions SHOW sessions TIMESERIES day HAVING sessions > 100 SINCE -30d UNTIL today ORDER BY day ASC
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. |
ShopifyQL
Examples
Description
Break down [`net_sales`](/docs/api/shopifyql/2026-01/schemas/sales_revenue/sales#salesmetric-propertydetail-netsales) by sales channel over the last 30 days, keeping only channels above $100. This example filters on the aggregated `net_sales`.
ShopifyQL
FROM sales SHOW net_sales GROUP BY sales_channel HAVING net_sales > 100 SINCE -30d UNTIL today ORDER BY net_sales DESCDescription
Find device types that recorded zero [`sessions`](/docs/api/shopifyql/2026-01/schemas/sessions_and_behavior/sessions#sessionsmetric-propertydetail-sessions) last month. This example filters with `= 0` to keep only the groups whose aggregated value equals zero.
ShopifyQL
FROM sessions SHOW sessions GROUP BY session_device_type HAVING sessions = 0 DURING last_month ORDER BY session_device_type ASC
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. |
ShopifyQL
Examples
Description
Compare [`sessions`](/docs/api/shopifyql/2026-01/schemas/sessions_and_behavior/sessions#sessionsmetric-propertydetail-sessions) last month for the desktop and mobile device types only. This example uses `IN` to keep several named groups in one condition, matching a dimension against a list of values.
ShopifyQL
FROM sessions SHOW sessions GROUP BY session_device_type HAVING session_device_type IN ('desktop', 'mobile') DURING last_month ORDER BY sessions DESCDescription
Find device types with between 100 and 500 [`sessions`](/docs/api/shopifyql/2026-01/schemas/sessions_and_behavior/sessions#sessionsmetric-propertydetail-sessions) last month. This example uses `BETWEEN` to keep groups with 100 to 500 sessions, including 100 and 500 themselves.
ShopifyQL
FROM sessions SHOW sessions GROUP BY session_device_type HAVING sessions BETWEEN 100 AND 500 DURING last_month ORDER BY sessions DESC
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. |
ShopifyQL
Examples
Description
Find [`net_sales`](/docs/api/shopifyql/2026-01/schemas/sales_revenue/sales#salesmetric-propertydetail-netsales) last month for products that have no product type set. This example uses `IS NULL` to test a dimension for missing values.
ShopifyQL
FROM sales SHOW net_sales GROUP BY product_type HAVING product_type IS NULL DURING last_month ORDER BY net_sales DESCDescription
Show last month's [`net_sales`](/docs/api/shopifyql/2026-01/schemas/sales_revenue/sales#salesmetric-propertydetail-netsales) for point-of-sale orders only, selected with `is_pos_sale IS TRUE`. This example uses `IS TRUE` to filter on a boolean dimension directly, without comparing it to a literal.
ShopifyQL
FROM sales SHOW net_sales GROUP BY is_pos_sale HAVING is_pos_sale IS TRUE DURING last_month ORDER BY net_sales DESC
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. |
ShopifyQL
Examples
Description
Break down [`net_sales`](/docs/api/shopifyql/2026-01/schemas/sales_revenue/sales#salesmetric-propertydetail-netsales) and `orders` by sales channel last month. This example uses `AND` to keep only channels above $1,000 in sales and more than 10 orders.
ShopifyQL
FROM sales SHOW net_sales, orders GROUP BY sales_channel HAVING net_sales > 1000 AND orders > 10 DURING last_month ORDER BY net_sales DESCDescription
Break down [`sessions`](/docs/api/shopifyql/2026-01/schemas/sessions_and_behavior/sessions#sessionsmetric-propertydetail-sessions) and `conversion_rate` by device type last month, keeping any device type that clears either threshold. This example uses `OR` to keep a group when at least one condition holds.
ShopifyQL
FROM sessions SHOW sessions, conversion_rate GROUP BY session_device_type HAVING sessions > 1000 OR conversion_rate > 0.05 DURING last_month ORDER BY sessions DESC
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. |
ShopifyQL
Examples
Description
Show the 10 landing pages with the most [`sessions`](/docs/api/shopifyql/2026-01/schemas/sessions_and_behavior/sessions#sessionsmetric-propertydetail-sessions) last month whose path includes `/products`. This example uses `CONTAINS` to match the substring anywhere in the dimension's value.
ShopifyQL
FROM sessions SHOW sessions GROUP BY landing_page_path HAVING landing_page_path CONTAINS '/products' DURING last_month ORDER BY sessions DESC LIMIT 10Description
Show the 10 highest-earning products last month whose title starts with `T`. This example uses [`STARTS WITH`](#string-and-array-matching), which matches only at the start of the value, unlike `CONTAINS`.
ShopifyQL
FROM sales SHOW net_sales GROUP BY product_title HAVING product_title STARTS WITH 'T' DURING last_month ORDER BY net_sales DESC LIMIT 10