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.
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/latest/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/latest/syntax/group-by), which [`WHERE`](/docs/api/shopifyql/latest/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/latest/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/latest/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/latest/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/latest/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/latest/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/latest/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/latest/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/latest/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/latest/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/latest/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/latest/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
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.
ShopifyQL
Examples
Description
Filter on the generated `total_sales__first_click` column that `FIRST_CLICK_ATTRIBUTION` produces, keeping referring channels whose first-click attributed sales exceed 1000. The filter targets the suffixed attribution column, not the base [`total_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-totalsales) metric.
ShopifyQL
FROM sales SHOW total_sales GROUP BY referring_channel WITH FIRST_CLICK_ATTRIBUTION HAVING total_sales__first_click > 1000 SINCE -30d UNTIL today ORDER BY total_sales__first_click DESCDescription
Filter on the generated `orders__last_click` column that `LAST_CLICK_ATTRIBUTION` produces, keeping referring channels whose last-click attributed orders fall between 10 and 100. The filter targets the suffixed attribution column, not the base [`orders`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-orders) metric.
ShopifyQL
FROM sales SHOW orders GROUP BY referring_channel WITH LAST_CLICK_ATTRIBUTION HAVING orders__last_click BETWEEN 10 AND 100 SINCE -30d UNTIL today ORDER BY orders__last_click DESC