Choose a version:

GROUP BY

Use GROUP BY to break aggregated results down by dimensions. Grouped dimensions shape the rows returned by a query. Use time dimensions for time-based groups, or use TOP to limit dimensions with many possible values.

Anchor to DimensionsDimensions

GROUP BY uses dimensions, which are fields defined by the selected table's schema. Available dimensions vary by table. Group by one dimension, or list multiple comma-separated dimensions in the order that ShopifyQL should apply the grouping.

A dimension returned by SHOW must appear in GROUP BY, unless it's a time dimension represented by TIMESERIES. If you rename a SHOW dimension with AS, then use the name after AS in GROUP BY. For multiple-table queries, use dimensions that the selected schemas can group together in a multi-fact query.

Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor

FROM payments

SHOW net_payments

GROUP BY payment_method WITH TOTALS

SINCE -30d UNTIL today

ORDER BY net_payments DESC

LIMIT 10

Examples

Description
Rank the top 10 payment methods by [`net_payments`](/docs/api/shopifyql/latest/schemas/finance_and_payments/payments#paymentsmetric-propertydetail-netpayments) over the last 30 days. This example uses `GROUP BY payment_method` to collapse transactions into one row per method, which [`ORDER BY`](/docs/api/shopifyql/latest/syntax/order-by) and [`LIMIT`](/docs/api/shopifyql/latest/syntax/limit) then rank and trim.
ShopifyQL
```
FROM payments
  SHOW net_payments
  GROUP BY payment_method WITH TOTALS
  SINCE -30d UNTIL today
  ORDER BY net_payments DESC
  LIMIT 10
```
Description
Break down [`sessions`](/docs/api/shopifyql/latest/schemas/sessions_and_behavior/sessions#sessionsmetric-propertydetail-sessions) by both day and device type for last month. This example lists two fields in `GROUP BY` to return a row for every combination.
ShopifyQL
```
FROM sessions
  SHOW sessions
  GROUP BY day, session_device_type WITH TOTALS
  DURING last_month
  ORDER BY day ASC, sessions DESC
```

Anchor to Time dimensionsTime dimensions

Group aggregated results by time dimensions, including intervals such as day and month, and date or clock values such as day_of_week and hour_of_day. GROUP BY returns only values that exist in the data for a grouped time dimension. Use TIMESERIES when the result should include values for one time dimension even when those values have no data.

Anchor to second second yyyy-MM-ddThh:mm:ss: Groups rows into one-second intervals.
Anchor to minute minute yyyy-MM-ddThh:mm: Groups rows into one-minute intervals.
Anchor to hour hour yyyy-MM-ddThh: Groups rows into one-hour intervals.
Anchor to day day yyyy-MM-dd: Groups rows into calendar days.
Anchor to week week yyyy-MM-dd: Groups rows into calendar weeks.
Anchor to month month yyyy-MM-dd: Groups rows into calendar months.
Anchor to quarter quarter yyyy-MM-dd: Groups rows into calendar quarters.
Anchor to year year yyyy-MM-dd: Groups rows into calendar years.
Anchor to hour_of_day hour_of_day 0-23: Groups rows by hour of the day, combining that hour across all dates.
Anchor to day_of_week day_of_week 0-6: Groups rows by day of the week, combining that weekday across all dates.
Anchor to week_of_year week_of_year 1-53: Groups rows by week of the year, combining that week across years.
Anchor to month_of_year month_of_year 1-12: Groups rows by month of the year, combining that month across years.

Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor

FROM sales

SHOW total_sales

GROUP BY month

SINCE startOfYear(0y) UNTIL today

ORDER BY month ASC

Examples

Description
Group [`total_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-totalsales) into one row per month for the year to date. This example uses `GROUP BY month` for plain monthly rows instead of the gap-filling series that [`TIMESERIES month`](/docs/api/shopifyql/latest/syntax/timeseries) returns.
ShopifyQL
```
FROM sales
  SHOW total_sales
  GROUP BY month
  SINCE startOfYear(0y) UNTIL today
  ORDER BY month ASC
```
Description
Total [`sessions`](/docs/api/shopifyql/latest/schemas/sessions_and_behavior/sessions#sessionsmetric-propertydetail-sessions) for last month into one row per day of the week. This example uses `GROUP BY day_of_week` to collapse dates into one row per weekday that has data.
ShopifyQL
```
FROM sessions
  SHOW sessions
  GROUP BY day_of_week
  DURING last_month
  ORDER BY day_of_week ASC
```

Anchor to TOPTOP

Use TOP to limit a high-cardinality dimension, such as product_title or shipping_country, to its most significant values. TOP count returns the count highest-ranked values, ranked by the metric the query aggregates. count must be a positive integer.

By default, ShopifyQL collects the remaining values into a single Other row, so the results still add up to the query's total. Optional keywords change this behavior:

ONLY, placed before TOP, drops the Other row and returns only the top values.
OVERALL, placed after the dimension, ranks values across the entire result instead of within each preceding GROUP BY dimension. It has no effect on the first dimension.

You can combine TOP with plain dimensions and use more than one TOP in a single GROUP BY.

Syntax

GROUP BY [ONLY] TOP count dimension [OVERALL][, ...]

Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor

FROM sales

SHOW total_sales

GROUP BY TOP 10 product_title

DURING last_month

ORDER BY total_sales DESC

Examples

Description
Show [`total_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-totalsales) for the 10 best-selling product titles last month. This example uses `GROUP BY TOP 10` to fold every other product into one remainder group.
ShopifyQL
```
FROM sales
  SHOW total_sales
  GROUP BY TOP 10 product_title
  DURING last_month
  ORDER BY total_sales DESC
```
Description
Find the five months with the most [`new_customer_records`](/docs/api/shopifyql/latest/schemas/customers/customers#customersmetric-propertydetail-newcustomerrecords) so far this year. This example uses plain `TOP 5` to bucket the rest into a remainder group, where `ONLY TOP 5` would drop them.
ShopifyQL
```
FROM customers
  SHOW new_customer_records
  GROUP BY TOP 5 month
  SINCE startOfYear(0y) UNTIL today
  ORDER BY new_customer_records DESC
```

Was this page helpful?