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.
Syntax
Anchor to DimensionsDimensions
GROUP BY uses dimensions, which are fields defined by the schema selected in FROM. Available dimensions vary by schema. 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-schema queries, use dimensions that the selected schemas can group together in a multi-fact query.
ShopifyQL
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 10Description
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 and . 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 secondsecondsecondyyyy-MM-ddThh:mm:ssyyyy-MM-ddThh:mm:ss
Groups rows into one-second intervals.
- Anchor to minuteminuteminuteyyyy-MM-ddThh:mmyyyy-MM-ddThh:mm
Groups rows into one-minute intervals.
- Anchor to hourhourhouryyyy-MM-ddThhyyyy-MM-ddThh
Groups rows into one-hour intervals.
- Anchor to daydaydayyyyy-MM-ddyyyy-MM-dd
Groups rows into calendar days.
- Anchor to weekweekweekyyyy-MM-ddyyyy-MM-dd
Groups rows into calendar weeks.
- Anchor to monthmonthmonthyyyy-MM-ddyyyy-MM-dd
Groups rows into calendar months.
- Anchor to quarterquarterquarteryyyy-MM-ddyyyy-MM-dd
Groups rows into calendar quarters.
- Anchor to yearyearyearyyyy-MM-ddyyyy-MM-dd
Groups rows into calendar years.
- Anchor to hour_of_dayhour_
of_ dayhour_ of_ day 0-230-23 Groups rows by hour of the day, combining that hour across all dates.
- Anchor to day_of_weekday_
of_ weekday_ of_ week 0-60-6 Groups rows by day of the week, combining that weekday across all dates.
- Anchor to week_of_yearweek_
of_ yearweek_ of_ year 1-531-53 Groups rows by week of the year, combining that week across years.
- Anchor to month_of_yearmonth_
of_ yearmonth_ of_ year 1-121-12 Groups rows by month of the year, combining that month across years.
ShopifyQL
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 ASCDescription
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
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 beforeTOP, drops theOtherrow and returns only the top values.OVERALL, placed after the dimension, ranks values across the entire result instead of within each precedingGROUP BYdimension. 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
ShopifyQL
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 DESCDescription
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