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 July 15, 2026

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
Clauses must appear in a fixed order: select, where, group by, pivot, order by, limit, offset. source
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

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():

Google Sheets

=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:

Google Sheets

=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:

terminal

# 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.

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) — Google
QUERY function — Google Docs Editors Help

Turn your sheet into an API in minutes

Paste a Google Sheet URL and get a live REST API and MCP server — no backend, no code, free to start.

Start free See pricing