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.

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.

Use TOP to limit a high-cardinality dimension, such as product_title or shipping_country, to its most significant values. TOP count keeps the first count values in the order that the query's ORDER BY sets, so ORDER BY total_sales DESC keeps the count values with the highest total_sales. count must be a positive integer.

TOP doesn't choose a metric on its own. Without an ORDER BY, or when ORDER BY only names a time dimension such as month, ShopifyQL falls back to a default ranking. In a query with no time dimension, or with OVERALL, it ranks by the first column of the result in ascending order. That column is the grouped dimension when SHOW doesn't list it, so SHOW total_sales GROUP BY TOP 5 product_title keeps the five product titles that sort first alphabetically. If SHOW lists a metric before the dimension, as in SHOW total_sales, product_title, the metric is the first column and TOP 5 keeps the five lowest total_sales values instead. With TIMESERIES and no OVERALL, the fallback ranks the dimension itself in ascending order within each period, so SHOW total_sales, product_title with TIMESERIES month still comes back alphabetical. State the ranking you want in ORDER BY: include the metric to rank by it, or use ORDER BY product_title ASC when you want alphabetical selection. When you also sort by a time dimension, list the time dimension first and the metric after it, as in ORDER BY month ASC, total_sales DESC. If ORDER BY lists the ranked dimension itself before the metric, the dimension decides the ranking.

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. With TIMESERIES, the time dimension counts as the preceding dimension, so use OVERALL to rank across the whole range instead of within each period. Without TIMESERIES, 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. For top values across a time series, such as monthly sales for your top product types, pair TOP in GROUP BY with TIMESERIES instead of grouping by the time dimension, and keep the metric in ORDER BY so the ranking doesn't fall back to the default order.