# Group by and aggregate in Google Sheets Counts, sums, and averages by category turn raw rows into a report. You can compute them inside the sheet with QUERY(), or fetch them pre-aggregated from a REST API. *Last updated: 2026-07-15 · Source: * ## Key facts - The `QUERY` function aggregates with `sum()`, `count()`, `avg()`, `min()` and `max()`, and the grouped result spills into the cells below the formula. ([source](https://developers.google.com/chart/interactive/docs/querylanguage)) - Clauses must appear in a **fixed order**: `select`, `where`, `group by`, `pivot`, `order by`, `limit`, `offset`. ([source](https://developers.google.com/chart/interactive/docs/querylanguage)) - Every column in `select` must be either **aggregated or named in `group by`** — the same rule SQL enforces, and the usual reason the formula errors. ([source](https://developers.google.com/chart/interactive/docs/querylanguage)) ## How do you GROUP BY in Google Sheets? Use the `QUERY()` function with a `group by` clause: `=QUERY(A:C, "select A, sum(C) group by A")`. It aggregates in the sheet with `sum()`, `count()`, `avg()`, `min()`, and `max()`, and the result spills into the cells below the formula. That works well for a total you want to *look at*. If you need the totals in an app, a dashboard, or an AI agent instead, compute them over the API — PasteSheet supports `group_by` with `count`, `sum`, and `avg` directly on the endpoint, so nothing has to be recomputed client-side. ## GROUP BY in the QUERY() function In-sheet, aggregate with `GROUP BY` and functions like `SUM()` and `COUNT()`: ```bash =QUERY(A1:D, "SELECT B, SUM(D) GROUP BY B") ``` ## Group by multiple columns, or filter first List several columns to group by more than one, and add a `WHERE` clause to aggregate only the rows you want: ```bash =QUERY(A1:E, "SELECT B, C, SUM(D) GROUP BY B, C") =QUERY(A1:E, "SELECT B, SUM(D) WHERE C = 'active' GROUP BY B") ``` ## The grouped result From raw sales rows (East 100, West 250, East 200, North 75), `SELECT region, SUM(amount) GROUP BY region` collapses them to one row per region: | Region | SUM(amount) | COUNT(id) | | --- | --- | --- | | East | 300 | 2 | | West | 250 | 1 | | North | 75 | 1 | ## Aggregation over the API PasteSheet computes aggregates server-side so your app receives grouped totals directly. Combine `group_by` with `count`, `sum`, and `avg`: ```bash # revenue and order count per region curl 'https://pastesheet.com/api/your-endpoint-id?group_by=region&sum=revenue&count=id' ``` ## Why GROUP BY errors The most common error comes from the core rule: **every column in `select` must be either aggregated or named in `group by`**. `SELECT B, C, SUM(D) GROUP BY B` fails because `C` is neither — add it to the `group by`, or wrap it in an aggregate. Clauses also have a fixed order: `select`, `where`, `group by`, `order by`, `limit`. ## Multiple metrics at once You can pass comma-separated columns to compute several metrics in one request — for example `sum=revenue,units&avg=price`. Aggregation is available on the **Pro plan**. See how it fits the wider query surface in [querying Google Sheets like SQL](https://pastesheet.com/guides/query-google-sheets-with-sql). ## Frequently asked questions ### How do I group by in a Google Sheets query? Use GROUP BY in the QUERY() function inside the sheet, or the group_by parameter with count/sum/avg over the PasteSheet REST API to get pre-aggregated results. ### Can I compute count, sum, and average? Yes. PasteSheet supports count, sum, and avg — each accepting comma-separated columns — grouped by any column via group_by. ### Which plan includes aggregation? Aggregation (count, sum, avg, group_by) is included on the Pro plan, across the REST API and MCP. ### Can I group by multiple columns? Yes. List them comma-separated: SELECT B, C, SUM(D) GROUP BY B, C. Every non-aggregated column in SELECT must also appear in GROUP BY. ### Why does my GROUP BY formula error? Usually because a column in SELECT is neither aggregated nor listed in GROUP BY, or the clauses are out of order. Every selected column must be inside an aggregate like SUM() or named in GROUP BY. ## Sources - [Query Language Reference (Google Visualization API)](https://developers.google.com/chart/interactive/docs/querylanguage) — Google - [QUERY function](https://support.google.com/docs/answer/3093343) — Google Docs Editors Help ## Related guides - [How to Query Google Sheets With SQL](https://pastesheet.com/guides/query-google-sheets-with-sql) — Query Google Sheets like SQL: the built-in QUERY() function for in-sheet queries, and a REST API for SQL-style filtering, sorting, and grouping from your app. - [Google Sheets Query: Sort & Order By](https://pastesheet.com/guides/google-sheets-query-order-by) — Sort Google Sheets query results by any column, ascending or descending, including by date. Use the QUERY() function or sort and order URL parameters. - [Turn Google Sheets Into a REST API (No Code)](https://pastesheet.com/guides/google-sheets-rest-api) — Turn a public or restricted Google Sheet into a live JSON REST API with filtering, sorting, and pagination — no backend, no code. --- [PasteSheet](https://pastesheet.com) turns any Google Sheet into a live REST API and MCP server for AI agents — no backend, no code. Canonical HTML version of this page: