> Note:
> The ShopifyQL API is being [sunset as of version 2024-07](https://shopify.dev/changelog/shopifyql-admin-graphql-api-sunset).
> Please consider using alternative sources of data in the [Admin GraphQL API](https://shopify.dev/docs/api/admin-graphql) for your use-cases, such as the [Orders API](https://shopify.dev/docs/api/admin-graphql/latest/queries/orders), and the [Web Pixels API](https://shopify.dev/docs/api/web-pixels-api).
> You can continue using ShopifyQL API on GraphQL API version `2024-04`.


This page describes ShopifyQL syntax, keywords, and keyword parameters.

The examples on this page use the orders and products datasets in Shopify stores. For information about available metrics, dimensions, and aggregates, refer to the [orders dataset](/docs/api/shopifyql/datasets/orders-dataset), [products dataset](/docs/api/shopifyql/datasets/products-dataset), or [payment attempts dataset](/docs/api/shopifyql/datasets/payment-attempts-dataset).

## Syntax

ShopifyQL follows the syntax below. You can place the entire query on one line or on separate lines.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="ShopifyQL"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM { table_name }

SHOW { column1 AS { alias } , column2 AS { alias } , ... }

VISUALIZE { column1 AS { alias } , column2 AS { alias } , ... }

  TYPE { visualization_type }

GROUP BY { dimension | date }

  ALL { date }

WHERE { condition }

SINCE { date_offset }

UNTIL { date_offset }

DURING { named_date_range }

COMPARE TO { named_date_range | relative_date_range }

ORDER BY { column } ASC | DESC

LIMIT { number }

-- Single line comment

/*
Multi line comment
*/
END_RAW_MD_CONTENT</script>

</div>
</p>


## Keywords

Keywords need to follow the [syntax order](#syntax), otherwise, errors occur.

This section describes the required keywords in ShopifyQL queries.

### Required keywords

A ShopifyQL query must contain at least the `FROM` and `SHOW` keywords.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using FROM and SHOW to return a numerical value of net sales"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(net_sales)
END_RAW_MD_CONTENT</script>

</div>
</p>


### Keyword order

Keywords need to be in the following order:

- `FROM`
- `SHOW` | `VISUALIZE`
- `GROUP BY`
- `WHERE`
- (`SINCE` & `UNTIL`) | `DURING`
- `COMPARE TO`
- `ORDER BY`
- `LIMIT`

### FROM

- `FROM { table_name }`
- `FROM` accepts one parameter, `table_name`, where `table_name` is a table.

### SHOW

- `SHOW { column1 AS { alias } , column2 AS { alias } , ... }`
- `SHOW` accepts any number of parameters, where each parameter is a column in a table or an expression.
  - Each parameter optionally accepts an `alias` using the [`AS`](#as) keyword.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using FROM to return the sum of net sales"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(net_sales)
END_RAW_MD_CONTENT</script>

</div>
</p>


### AS

- `AS { alias }`
- `AS` accepts one parameter, which is an alias for a column name in a table, or an alias for the return value of an [aggregate function](#aggregate-functions). If an alias has a space in the name, then surround the alias with double quotes.
- `AS` can used with both the [`SHOW`](#show) and [`VISUALIZE`](#visualize) keywords.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using AS to alias the sum of net_sales as `Net Sales`"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(net_sales)  AS "Net Sales"
END_RAW_MD_CONTENT</script>

</div>
</p>


> Note:
> The double quotes are necessary when the alias contains a space.

### VISUALIZE

- `VISUALIZE { column1 AS { alias } , column2 AS { alias } , ... }`
- `VISUALIZE` accepts any number of parameters, where each parameter is a column in a table or an expression.
  - Each parameter optionally accepts an `alias` using the [`AS`](#as) keyword.
- `VISUALIZE` returns axis labels and data formatting information for creating data visualizations.
- Optionally accepts a `visualization_type` using the [TYPE](#type) keyword.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Return axis labels and data formatting for the sum of the net sales grouped by month for the last year"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
VISUALIZE sum(net_sales)
TYPE line
GROUP BY month ALL
SINCE -1y
UNTIL today
END_RAW_MD_CONTENT</script>

</div>
</p>


 The sales are depicted as a single line, with the x-axis labeled as `month`, and the y-axis as `sum_net_sales`.

#### TYPE

- `TYPE { visualization_type }`
- `TYPE` accepts one parameter, `visualization_type`, where `visualization_type` is `line` or `bar`.
  - `line` returns a line graph.
  - `bar` returns a bar graph.
- `TYPE` is an optional keyword. If `TYPE` isn't used, then ShopifyQL returns a visualization that's appropriate for the submitted query.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Return axis labels and data formatting for a line chart, for the total sales by month over the last year"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
VISUALIZE sum(net_sales)
TYPE line
GROUP BY month ALL
SINCE -1y
UNTIL today
END_RAW_MD_CONTENT</script>

</div>
</p>


### GROUP BY

- `GROUP BY { dimension | date }`
- `GROUP BY` accepts any number of parameters, where each is a `dimension` or [time dimension](#time-dimensions). A `dimension` is a field in a table.
  - Each parameter optionally accepts an `alias_name` using the [`AS`](#as) keyword.
- If there isn't a return value for the specified `dimension`, `expression`, or `date`, then the `dimension`, `expression`, or `date` isn't returned. If you want to return the `dimension`, `expression`, or `date` when there's no data present, then you can use the [`ALL`](#all) modifier.

> Note:
> `GROUP BY` is similar to the [SQL `GROUP BY`](https://www.w3schools.com/sql/sql_groupby.asp) statement.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using GROUP BY to sort by shipping country"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(net_sales)
GROUP BY shipping_country
END_RAW_MD_CONTENT</script>

</div>
</p>


<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using GROUP BY to sort by month"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(net_sales)
GROUP BY month
END_RAW_MD_CONTENT</script>

</div>
</p>


#### ALL

- `GROUP BY { dimension | date } ALL`
- `ALL` is an optional [`GROUP BY`](#group-by) modifier which can only be used with a `date`.
- The `ALL` modifier fills in zeros for any `date` where data isn't present.
- The `ALL` modifier enables you to retrieve continuous date periods. This allows you to get continuous date periods without having to perform joins to date lookup tables.
- When using the `ALL` modifier [`SINCE`](#since-and-until) or [`DURING`](#during) must also be specified.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using GROUP BY with ALL modifier to retrieve the net sales for each hour in the last 24 hours, regardless of whether there was a sale in each hour"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(net_sales)
GROUP BY hour ALL
SINCE -1d
UNTIL today
ORDER BY hour DESC
END_RAW_MD_CONTENT</script>

</div>
</p>


<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using GROUP BY with and without an ALL modifier to retrieve all months for each shipping country"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(net_sales)
GROUP BY shipping_country, month ALL
SINCE -1y
UNTIL yesterday
ORDER BY shipping_country ASC, month ASC
END_RAW_MD_CONTENT</script>

</div>
</p>


### WHERE

- `WHERE { condition }`
- `WHERE` accepts one parameter, `condition`, where `condition` is an expression that consists of one or more [comparison operators](#comparison-operators) and [logical operators](#logical-operators).
- `WHERE` filters the results of an entire query based on the specified `condition`.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using WHERE and the = operator to filter the gross sales for all orders that were shipped to the United States"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(gross_sales)
WHERE shipping_country = ‘United States’
END_RAW_MD_CONTENT</script>

</div>
</p>


### SINCE and UNTIL

- `SINCE { date_offset }`
  - `SINCE` accepts one parameter, `date_offset`, where `date_offset` is a [date range operator](#date-range-operators). The date range operator that's used for `start_date_offset` sets a starting date in a date range. This date is included in the range.

- `UNTIL { date_offset }`
  - `UNTIL` accepts one parameter, `date_offset`, where `date_offset` is a [date range operator](#date-range-operators). The date range operator that's used for `end_date_offset` sets an ending date in a date range. This date is included in the range.
  - If `UNTIL` isn't used in a query, then `today` is used as the ending date.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using SINCE and UNTIL to filter the net sales since and including March 1, 2020 until and including August 8, 2020"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(net_sales)
SINCE 2020-03-01
UNTIL 2020-08-01
END_RAW_MD_CONTENT</script>

</div>
</p>


<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using SINCE and UNTIL to filter the net sales since and including 30 days ago until and including yesterday"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(net_sales)
SINCE -30d
UNTIL yesterday
END_RAW_MD_CONTENT</script>

</div>
</p>


### DURING

- `DURING { named_date_range }`
- `DURING` accepts one parameter, `named_date_range`, where `named_date_range` is a [named date range operator](#named-date-range-operators).
- `DURING` is an optional keyword that replaces [`SINCE` and `UNTIL`](#since-and-until) statements.
- This keyword helps to filter the query results for known time periods such as a calendar year or a specific month, or to filter the query results for date ranges that have different dates every year, such as Black Friday Cyber Monday.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using DURING to filter the net sales during Black Friday Cyber Monday 2021"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(net_sales)
GROUP BY day
DURING bfcm2021
END_RAW_MD_CONTENT</script>

</div>
</p>


### COMPARE TO

- `COMPARE TO { named_date_range | relative_date_range }`
- `COMPARE TO` is an optional keyword which is paired with [`SINCE` and `UNTIL`](#since-and-until) or [`DURING`](#during).
  - When paired with [`SINCE` and `UNTIL`](#since-and-until) `COMPARE TO` accepts a `relative_date_range` (see [relative date range operators](#relative-date-range-operators)).
      - While using the `relative_date_range` `previous_year` the [`SINCE` and `UNTIL`](#since-and-until) specified length of time must be a year or less.
  - When paired with [`DURING`](#during), `COMPARE TO` accepts either a `named_date_range` (see [named date range operators](#named-date-range-operators)) or a `relative_date_range` (see [relative date range operators](#relative-date-range-operators)).
      - While using a `named_date_range` it must match the length of the parameter in [`DURING`](#during).
- This keyword allows you to compare data across the `date_offset` in [`SINCE` and `UNTIL`](#since-and-until) or [`DURING`](#during) to the `COMPARE TO` `named_date_range` or `relative_date_range`.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using DURING and COMPARE TO to compare the net sales during Black Friday Cyber Monday 2022 and Black Friday Cyber Monday 2021"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(net_sales)
GROUP BY day
DURING bfcm2022
COMPARE TO bfcm2021
END_RAW_MD_CONTENT</script>

</div>
</p>


<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using SINCE and COMPARE TO to compare the net sales previous month to the same month in the previous year"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(net_sales)
GROUP BY day
SINCE -1m
UNTIL -0m
COMPARE TO previous_year
END_RAW_MD_CONTENT</script>

</div>
</p>


### ORDER BY

- `ORDER BY { column } ASC | DESC`
- `ORDER BY` can accept a list of one or more parameters. Each parameter consists of one or two elements, `column`, and an optional `ASC` or `DESC`. `column` is a column in a table, and `ASC` or `DESC` change the behavior of the returned results.
  - The `ASC` parameter is used after the `column` parameter, and indicates that the returned query results are sorted in an ascending order.
  - The `DESC` parameter is used after the `column` parameter, and indicates that the returned query results are sorted in a descending order.
  - If the `ASC` or `DESC` parameters aren't used, then the default sorting order is ascending.
- If `ORDER BY` has multiple columns, then the results are first sorted by the first `column`, and then sorted by the second `column`, and so on.
- The columns used in the `ORDER BY` parameters must be present in either the `SHOW`, `BY` or `OVER` parameter list.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using ORDER BY to retrieve the net sales for each product, and then sort the products in a descending order based on the sales"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM products
SHOW sum(net_sales) as sales
GROUP BY product_title
ORDER BY sales DESC
END_RAW_MD_CONTENT</script>

</div>
</p>


<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using ORDER BY with multiple fields to sort the net sales for all products and variants over the last year. The results are first sorted in alphabetical order by product title, and then in reverse alphabetical order by product type"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM products
SHOW sum(net_sales)
GROUP BY product_title, product_type
SINCE -1y
UNTIL today
ORDER BY product_title, product_type DESC
END_RAW_MD_CONTENT</script>

</div>
</p>


### LIMIT

- `LIMIT { number }`
- `LIMIT` accepts one parameter, `number`, where `number` is a number that represents how many rows that you want the query to return.
- `LIMIT` enables you to understand the data in each column without returning all of the data in the table. This is useful for larger tables where queries can take longer to return values. You can also use `LIMIT` with [`ORDER BY`](#order-by) to create lists of the top or bottom-most X. For example, the top five products.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using LIMIT to return a limited number of rows in a table to understand the relationship between the fields that you&#39;ve selected"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM products
SHOW net_sales, gross_sales, returns, taxes, discounts
GROUP BY  product_title, product_type, product_id
SINCE -1y
UNTIL today
LIMIT 5
END_RAW_MD_CONTENT</script>

</div>
</p>


<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using LIMIT with ORDER BY to create a list of the top-10 selling products over the last 3 months"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM products
SHOW sum(gross_sales) as total_gross_sales
GROUP BY product_title
SINCE -3m
UNTIL today
ORDER BY total_gross_sales DESC
LIMIT 10
END_RAW_MD_CONTENT</script>

</div>
</p>


## Time dimensions

The time functions are abstracted so you don’t have to keep track of which date field corresponds to the grain of the available datasets. The following date fields are available as time dimensions in ShopifyQL:

| Date field     | Description                             |
|----------------|-----------------------------------------|
| `hour`         | Groups by hour of calendar day          |
| `day`          | Groups by calendar day                  |
| `week`         | Groups by calendar week                 |
| `month`        | Groups by calendar month                |
| `quarter`      | Groups by calendar quarter              |
| `year`         | Groups by calendar year                 |
| `hour_of_day`  | Groups by 24 hours (1, 2, ..., 24)      |
| `day_of_week`  | Groups by day of week (M, T, W, ..., S) |
| `week_of_year` | Groups by week of year (1, 2, ..., 52)  |

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using the day time dimension to group by day over the last 30 days"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(gross_sales)
GROUP BY day ALL
SINCE -30d
UNTIL today
END_RAW_MD_CONTENT</script>

</div>
</p>


<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using the day_of_week time dimension to group by day_of_week over the last 30 days"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(gross_sales)
GROUP BY day_of_week ALL
SINCE -30d
UNTIL today
END_RAW_MD_CONTENT</script>

</div>
</p>


## Date range operators

You can use the following date range operators as well as a [named date range operator](#named-date-range-operators) in [`SINCE` and `UNTIL`](#since-and-until) statements.

| Date range operator | Description                                                             |
|---------------------|-------------------------------------------------------------------------|
| `{-} {#} d`         | The number of calendar days ago from the day that the query was run     |
| `{-} {#} w`         | The number of calendar weeks ago from the day that the query was run    |
| `{-} {#} m`         | The number of calendar months ago from the day that the query was run   |
| `{-} {#} q`         | The number of calendar quarters ago from the day that the query was run |
| `{-} {#} y`         | The number of calendar years ago from the day that the query was run    |
| `yyyy-mm-dd`        | A specific date                                                         |

> Note:
> Date range operators truncate to the start of the time grain (day, week, month, quarter, and year) when used with `SINCE` and to the end of the time grain when used with `UNTIL`.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using date range operators to get the gross sales from 2020-01-01 through 2022-10-30 (Assuming current date of 2022-11-08)"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(gross_sales)
SINCE -2y
UNTIL -2w
END_RAW_MD_CONTENT</script>

</div>
</p>


### Named date range operators

[`SINCE` and `UNTIL`](#since-and-until), [`DURING`](#during), and [`COMPARE TO`](#compare-to) accept any of the following named date range date operators below:

| Date Range Operator | Description                                                      |
|---------------------|------------------------------------------------------------------|
| `today`             | The day that the query was run                                   |
| `yesterday`         | The previous 24-hour period from the time that the query was run |
| `this_week`         | The current calendar week                                        |
| `this_month`        | The current calendar month                                       |
| `this_quarter`      | The current calendar quarter                                     |
| `this_year`         | The current calendar year                                        |
| `last_week`         | The previous calendar week                                       |
| `last_month`        | The previous calendar month                                      |
| `last_quarter`      | The previous calendar quarter                                    |
| `last_year`         | The previous calendar year                                       |
| `bfcm2022`          | November 25 to November 28 2022                                  |
| `bfcm2021`          | November 26 to November 29 2021                                  |
| `bfcm2020`          | November 27 to November 30 2020                                  |
| `bfcm2019`          | November 29 to December 2 2019                                   |
| `bfcm2018`          | November 23 to November 26 2018                                  |
| `bfcm2017`          | November 24 to November 27 2017                                  |
| `bfcm2016`          | November 25 to November 28 2016                                  |

### Relative date range operators

Relative operators return the same length of time as the base date range, shifted back by the specified period. [`COMPARE TO`](#compare-to) accepts the following relative date range operators:

| Date Range Operator | Description                                                      |
|---------------------|------------------------------------------------------------------|
| `previous_period`   | One period before the base date range                            |
| `previous_year`     | One year before the base date range                              |

The following table demonstrates how `previous_period` and `previous_year` work with base periods specified by [`DURING`](#during):

| `DURING`      | `COMPARE TO previous_period`  | `COMPARE TO previous_year`                                         |
|---------------|-------------------------------|--------------------------------------------------------------------|
| `today`       | `yesterday`                   | same date, in the previous year (Feb 29 = Mar 1, Feb 28 = Feb 28)  |
| `yesterday`   | day before `yesterday`        | yesterday, in the previous year (Feb 29 = Mar 1, Feb 28 = Feb 28)  |
| `this_week`   | `last_week`                   | same week of year as `this_week` but the in previous year          |
| `last_week`   | week before `last_week`       | same week of year as `last_week` but the in previous year          |
| `this_month`  | `last_month`                  | same month as `this_month` but in the previous year                |
| `last_month`  | month before `last_month`     | same month as `last_month` but in the previous year                |
| `this_quarter`| `last_quarter`                | same quarter as `this_quarter` but in the previous year            |
| `last_quarter`| quarter before `last_quarter` | same quarter as `last_quarter` but in the previous year            |
| `this_year`   | `last_year`                   | `last_year`                                                        |
| `last_year`   | year before `last_year`       | year before `last_year`                                            |
| `bfcm2022`    | `bfcm2021`                    | `bfcm2021`                                                         |
| `bfcm2021`    | `bfcm2020`                    | `bfcm2020`                                                         |

The following table demonstrates how `previous_period` and `previous_year` work with base periods specified by [`SINCE` and `UNTIL`](#since-and-until):

| `SINCE`       | `UNTIL`       | `COMPARE TO previous_period`                                          | `COMPARE TO previous_year`                                                    |
|---------------|---------------|-----------------------------------------------------------------------|-------------------------------------------------------------------------------|
| `today`       | `today`       | `yesterday`                                                           | same date, in the previous year (Feb 29 = Mar 1, Feb 28 = Feb 28)             |
| `yesterday`   | `yesterday`   | day before `yesterday`                                                | yesterday, in the previous year (Feb 29 = Mar 1, Feb 28 = Feb 28)             |
| `this_week`   | `this_week`   | `last_week`                                                           | same week of year as `this_week` but the in previous year                     |
| `bfcm2022`    | `bfcm2022`    | `bfcm2021`                                                            | `bfcm2021`                                                                    |
| `yesterday`   | `today`       | the same length of time as the base period but before the base period | the same two days but in the previous year                                    |
| `-2m`         | `1m`          | the same length of time as the base period but before the base period | the start of the previous two months to the end of last month but in the previous year |
| `-1m`         | `this_week`   | the same length of time as the base period but before the base period | the start of last month to the end of this week but in the previous year      |
| `2021-11-03`  | `2022-11-15`  | the same length of time as the base period but before the base period | invalid, the period of 377 days is greater than a single year                 |

> Note:
> When using `SINCE` and `UNTIL` with `COMPARE TO previous_period` the previous period will be precisely the same length of time as defined in the `SINCE` and `UNTIL` base period. This is true even in special cases such as leap years, daylight savings, and months with varying number of days.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using SINCE and UNTIL with COMPARE TO previous_period. The base period is 27 days long therefore the previous_period will be 2022-01-05 to 2022-02-01"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(net_sales)
SINCE 2022-02-01
UNTIL 2022-02-28
COMPARE TO previous_period
END_RAW_MD_CONTENT</script>

</div>
</p>


## Comparison operators

You can use the following comparison operators in [`WHERE`](#where) statements.

| Comparison operator | Description              |
|---------------------|--------------------------|
| `=`                 | Equal to                 |
| `!=`                | Not equal to             |
| `<`                 | Less than                |
| `>`                 | Greater than             |
| `<=`                | Less than or equal to    |
| `>=`                | Greater than or equal to |

## Logical operators

You can use one or more of the following logical operators in [`WHERE`](#where) statements.

| Logical operator | Description                                                                                |
|------------------|--------------------------------------------------------------------------------------------|
| `AND`            | Return all rows where the conditions that are separated by an `AND` are satisfied          |
| `OR`             | Return all rows where either of the conditions that are separated by an `OR` are satisfied |
| `NOT`            | Return all rows where the conditions aren't satisfied                                      |

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using WHERE and the = operator to filter the gross sales for all orders that were shipped to the United States and sold on the Online Store or POS sales channels"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(gross_sales)
WHERE shipping_country = 'United States' AND (sales_channel = 'Online Store' OR sales_channel = 'Point of Sale')
END_RAW_MD_CONTENT</script>

</div>
</p>


## Mathematical operators

You can use the following mathematical operators on numerical data.

| Mathematical operator | Description          |
|-----------------------|----------------------|
| `+`                   | Add two numbers      |
| `-`                   | Subtract two numbers |
| `*`                   | Multiple two numbers |
| `/`                   | Divide two numbers   |

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using the addition and division operators to calculate order values for each billing region over the last year"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(net_sales) + sum(returns) AS total_sales , (sum(net_sales) + sum(returns))/sum(orders) AS sales_per_order
GROUP BY billing_region
SINCE -1y
UNTIL today
END_RAW_MD_CONTENT</script>

</div>
</p>


## Aggregate functions

You can use the following functions to aggregate columns, and group columns by dimensions.

| Aggregate function  | Description                                    |
|---------------------|------------------------------------------------|
| `count()`           | Return the number of instances in a result set |
| `sum()`             | Return the sum of values in a result set       |
| `min()`             | Return the lowest value in a result set        |
| `max()`             | Return the highest value in a result set       |
| `avg()`             | Return the average value in a result set       |

The `sum()`, `min()`, `max()`, and `avg()` functions can only be used with numerical values, while `count()` can be used to count different instances of dimensional attributes. You can't use aggregated fields as arguments in the functions.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using the sum() function on an aggregated field result in an error since that field has already been aggregated"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum (average_order_value)
END_RAW_MD_CONTENT</script>

</div>
</p>


The following query returns the aggregated sum of net sales and ordered quantity as a result of the `sum()` function, and the count of cities as a result of the `count()` function. These metrics are broken down by region for all regions in the United States in 2021.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Valid way to mix aggregated fields with aggregate functions"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(net_sales), sum(ordered_product_quantity), count(billing_city) AS number_of_cities, average_order_value
GROUP BY billing_region
WHERE billing_country = ‘United States’
SINCE 2021-01-01
UNTIL 2021-12-31
END_RAW_MD_CONTENT</script>

</div>
</p>


## Comments

Single line comments start with `--` and end at the end of the line.

Multi-line comments start with `/*` and end with `*/`.

You can use comments to explain sections of ShopifyQL statements, or to prevent the execution of
a ShopifyQL statement. Any text within a comment will be ignored during execution time.

<p>
<div class="react-code-block" data-preset="file">
<div class="react-code-block-preload ThemeMode-dim">
<div class="react-code-block-preload-bar "></div>
<div class="react-code-block-preload-placeholder-container">
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>
<div class="react-code-block-preload-code-container">
<div class="react-code-block-preload-codeline-number"></div>
<div class="react-code-block-preload-codeline"></div>
</div>

</div>
</div>

<script data-option="filename" data-value="Using comments to prevent execution of statements or add explanations"></script>

<script type="text/plain" data-language="none">
RAW_MD_CONTENTFROM orders
SHOW sum(net_sales), sum(ordered_product_quantity), count(billing_city) AS number_of_cities, average_order_value
-- the line below has been commented out and will not run
-- GROUP BY billing_region
WHERE billing_country = ‘United States’
/*
this line and the two lines below it have been commented out and will not run
SINCE 2021-01-01
UNTIL 2021-12-31
*/
END_RAW_MD_CONTENT</script>

</div>
</p>