--- title: HAVING description: Filter ShopifyQL results after aggregation. api_version: 2026-07 source_url: html: 'https://shopify.dev/docs/api/shopifyql/latest/syntax/having' md: 'https://shopify.dev/docs/api/shopifyql/latest/syntax/having.md' api_name: shopifyql --- # HAVING Use `HAVING` to filter query results after ShopifyQL calculates metrics. To filter rows before aggregation, use [`WHERE`](https://shopify.dev/docs/api/shopifyql/latest/syntax/where). A `HAVING` condition can use [returned metrics, aliases, supported function calls, and dimensions that are part of the query result](#conditions). It can also filter [generated result columns](#generated-result-columns) when an attribution modifier generates them. **Note:** 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 ```shopifyql HAVING condition condition: boolean_expression value comparison_operator value value [NOT] IN (value, ...) value [NOT] BETWEEN low AND high value STARTS WITH|ENDS WITH|[NOT] CONTAINS value value IS [NOT] NULL|TRUE|FALSE NOT condition condition AND|OR condition (condition) value: returned_metric result_dimension alias literal supported_function_call ``` *** ## Conditions 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`](https://shopify.dev/docs/api/shopifyql/latest/syntax/from-and-show#show), or selected by [`VISUALIZE`](https://shopify.dev/docs/api/shopifyql/latest/syntax/visualize#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`](https://shopify.dev/docs/api/shopifyql/latest/syntax/group-by), or the time dimension selected by [`TIMESERIES`](https://shopify.dev/docs/api/shopifyql/latest/syntax/timeseries). If `SHOW` renames a returned field with [`AS`](https://shopify.dev/docs/api/shopifyql/latest/syntax/from-and-show#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`. Examples ### 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 ```shopifyql FROM sales SHOW net_sales GROUP BY product_title HAVING net_sales > 1000 DURING last_month ORDER BY net_sales DESC LIMIT 10 ``` * #### ##### Description 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 ```shopifyql FROM sessions SHOW sessions TIMESERIES day HAVING sessions > 100 SINCE -30d UNTIL today ORDER BY day ASC ``` *** ## Comparison 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 | ` = ` | Matches values equal to the right-hand side. Works with compatible scalar values. | | not equals | ` != ` | Matches values not equal to the right-hand side. Works with compatible scalar values. | | less than | ` < ` | Matches values less than the right-hand side. Use with ordered values such as numbers, money, dates, or text. | | less than or equal | ` <= ` | Matches values less than or equal to the right-hand side. Use with ordered values such as numbers, money, dates, or text. | | greater than | ` > ` | Matches values greater than the right-hand side. Use with ordered values such as numbers, money, dates, or text. | | greater than or equal | ` >= ` | Matches values greater than or equal to the right-hand side. Use with ordered values such as numbers, money, dates, or text. | Examples ### 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 ```shopifyql FROM sales SHOW net_sales GROUP BY sales_channel HAVING net_sales > 100 SINCE -30d UNTIL today ORDER BY net_sales DESC ``` * #### ##### Description 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 ```shopifyql FROM sessions SHOW sessions GROUP BY session_device_type HAVING sessions = 0 DURING last_month ORDER BY session_device_type ASC ``` *** ## Range 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 | ` IN (, ...)` | Matches any compatible value in a list. | | NOT IN | ` NOT IN (, ...)` | Excludes any compatible value in a list. | | BETWEEN | ` BETWEEN AND ` | Matches values within an inclusive range. Use with ordered values such as numbers, money, dates, or text. | | NOT BETWEEN | ` NOT BETWEEN AND ` | Matches values outside an inclusive range. Use with ordered values such as numbers, money, dates, or text. | Examples ### 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 ```shopifyql FROM sessions SHOW sessions GROUP BY session_device_type HAVING session_device_type IN ('desktop', 'mobile') DURING last_month ORDER BY sessions DESC ``` * #### ##### Description 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 ```shopifyql FROM sessions SHOW sessions GROUP BY session_device_type HAVING sessions BETWEEN 100 AND 500 DURING last_month ORDER BY sessions DESC ``` *** ## Null 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. | Examples ### 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 ```shopifyql FROM sales SHOW net_sales GROUP BY product_type HAVING product_type IS NULL DURING last_month ORDER BY net_sales DESC ``` * #### ##### Description 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 ```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 ``` *** ## Logical 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 | ` AND ` | Requires both conditions to match. | | OR | ` OR ` | Requires either condition to match. | | NOT | `NOT ` | Negates a condition. | Examples ### 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 ```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 DESC ``` * #### ##### Description 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 ```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 ``` *** ## String 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 | ` STARTS WITH ` | Matches string-like values with the specified prefix. | | ENDS WITH | ` ENDS WITH ` | Matches string-like values with the specified suffix. | | CONTAINS | ` CONTAINS ` | Matches string-like values containing the specified substring, or arrays containing the specified element. | | NOT CONTAINS | ` NOT CONTAINS ` | Matches string-like values or arrays that do not contain the specified value. | Examples ### 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 ```shopifyql FROM sessions SHOW sessions GROUP BY landing_page_path HAVING landing_page_path CONTAINS '/products' DURING last_month ORDER BY sessions DESC LIMIT 10 ``` * #### ##### Description 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 ```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 ``` *** ## Generated 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](https://shopify.dev/docs/api/shopifyql/latest/syntax/with#attribution-result-columns). Examples ### 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 ```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 DESC ``` * #### ##### Description 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 ```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 ``` ***