Why does Google Sheets QUERY say "cannot group without agg"?

The error fires when a column appears in the SELECT clause of a QUERY with GROUP BY, but isn't listed in GROUP BY and isn't wrapped in an aggregate function (SUM, AVG, COUNT, MAX, MIN). The Google Visualization API query language requires every SELECT column to be either a GROUP BY dimension or an aggregated measure. There's no partial workaround - every column must resolve to a single value per group.

Can I use QUERY GROUP BY with data from multiple tabs?

No, not directly. QUERY only accepts a single contiguous range, so referencing `'P&L'!C:C` and `Assumptions!$B$3` in the same query string isn't possible without string concatenation - which breaks on date formatting. For cross-tab aggregations with dynamic criteria, use SUMIFS: `=SUMIFS('P&L'!C:C,'P&L'!B:B,">="&Assumptions!$B$3,'P&L'!D:D,Region!$A2)`. That formula handles multi-tab references and parameterized date ranges natively.

How do I aggregate multiple columns in one QUERY GROUP BY?

List each measure as a separate aggregate in SELECT: `SELECT B, SUM(C), AVG(D), MAX(E) GROUP BY B`. Each column that isn't B must have its own function. Expressions built from aggregates also work: `(SUM(C)-SUM(D))/SUM(C)` for a gross margin calculation. The key is that every term in any expression must be aggregated - `SUM(C)/C` fails because the denominator is unaggregated.

Is SUMIFS faster than QUERY GROUP BY on large datasets?

Generally yes, for datasets over 10,000 rows. QUERY re-executes a full parse and evaluation pass on every recalc trigger. On a 50,000-row transaction log, QUERY GROUP BY can take 3-5 seconds to return results, while SUMIFS on the same data typically completes in under 1 second. For datasets under 5,000 rows the difference is negligible, and QUERY's syntax readability may be worth the minor performance cost.

Why does my QUERY work without GROUP BY but fail when I add it?

Without GROUP BY, QUERY returns every matching row from the range - no aggregation required, so any column can appear in SELECT. Adding GROUP BY switches QUERY into aggregation mode, where every non-grouped column must have an aggregate function. If a column worked in the no-GROUP BY version and fails after you add it, it's unaggregated. Either wrap it in SUM/COUNT/AVG/MAX/MIN, add it to GROUP BY, or remove it from SELECT entirely.

Google Sheets QUERY Cannot Group Without Agg Fix

Why the Error Fires

Google's QUERY function runs on the Google Visualization API query language, which follows strict SQL-like rules. The documentation states: "When using GROUP BY, each column in the SELECT clause must either be listed in the GROUP BY clause, or use an aggregation function."

No ambiguity, no exceptions. Every column is either a dimension (listed in GROUP BY) or a measure (wrapped in an aggregate). The moment you mix those roles, QUERY stops with cannot group without agg.

Here's a concrete example. You have a Revenue tab with columns: Date (A), Region (B), SKU (C), Revenue (D), COGS (E). You want revenue by region:

=QUERY('Revenue'!A:E, "SELECT B, C, SUM(D) GROUP BY B", 1)

This returns the error because column C (SKU) appears in SELECT but isn't in GROUP BY and isn't aggregated. Two valid fixes:

Option 1 - Add SKU to GROUP BY (returns one row per region-SKU pair):

=QUERY('Revenue'!A:E, "SELECT B, C, SUM(D) GROUP BY B, C", 1)

Option 2 - Drop SKU from SELECT (returns one row per region):

=QUERY('Revenue'!A:E, "SELECT B, SUM(D) GROUP BY B", 1)

Which fix is right depends on the output you need, not on QUERY syntax. If you want revenue by region alone, drop C. If you want revenue by region-SKU, keep C and add it to GROUP BY.

The Column Audit Pattern

For a board pack query with 6+ columns, audit every SELECT column before running. Here's a Q2 actuals pull that groups correctly:

=QUERY(
  'Revenue'!A:F,
  "SELECT B, SUM(D), SUM(E), SUM(D)-SUM(E)
   WHERE A >= date '2026-04-01' AND A <= date '2026-06-30'
   GROUP BY B
   LABEL SUM(D) 'Revenue', SUM(E) 'COGS', SUM(D)-SUM(E) 'Gross Profit'",
  1
)

Region (B) is the dimension - it goes in GROUP BY. Revenue, COGS, and Gross Profit are measures - every one is wrapped in SUM() or expressed as a combination of aggregates. The expression SUM(D)-SUM(E) works precisely because both components are aggregated. The result per group is a single value, which is all QUERY requires.

QUERY GROUP BY vs. SUMIFS: Choosing the Right Tool

QUERY's GROUP BY is clean for single-tab aggregation. For multi-tab financial models, SUMIFS usually wins. Here's the honest comparison:

Scenario	QUERY GROUP BY	SUMIFS / SUMPRODUCT
Single-tab aggregation	Clean, SQL-like syntax	More verbose
Cross-tab references	Not supported natively	Native, reliable
Dynamic date from Assumptions tab	String concat required	`">=" & Assumptions!$B$3`
Multiple filter conditions	WHERE clause	Stacked SUMIFS criteria
Datasets over 10,000 rows	Slower on recalc	Faster
Unknown set of dimension values	Returns all rows automatically	Requires hardcoded criteria
Debugging grouping errors	`cannot group without agg`	`#VALUE!` on bad ranges

If your quarterly board pack pulls actuals where the date range lives in Assumptions!$B$3:$B$4 and revenue sits in 'P&L'!C:C, SUMIFS handles it without QUERY's string concatenation workaround:

=SUMIFS(
  'P&L'!C:C,
  'P&L'!B:B, ">=" & Assumptions!$B$3,
  'P&L'!B:B, "<=" & Assumptions!$B$4,
  'P&L'!D:D, Returns!$A2
)

QUERY can't directly reference Assumptions!$B$3 inside the query string. You'd need to concatenate it as a text value, which breaks on date formatting and produces errors that look unrelated to the original problem. SUMIFS takes the cell reference natively.

When QUERY GROUP BY Is Worth It

QUERY earns its place when you don't know the distinct values in advance. If you have 14 active SKUs and need contribution margin by SKU for a gross margin analysis (38.5% target, current blended at 34.1%), QUERY returns all 14 rows without hardcoding:

=QUERY(
  'Sales'!A:G,
  "SELECT C, SUM(D), SUM(E), (SUM(D)-SUM(E))/SUM(D)
   GROUP BY C
   ORDER BY (SUM(D)-SUM(E))/SUM(D) DESC
   LABEL C 'SKU', SUM(D) 'Revenue', SUM(E) 'COGS'",
  1
)

The margin expression (SUM(D)-SUM(E))/SUM(D) doesn't trigger a grouping error because every component is aggregated. One value per group, every time. SUMIFS would require one hardcoded row per SKU, which breaks the moment a new SKU appears in the data.

The Non-Obvious Edge Cases

Labels: The LABEL clause doesn't cause grouping errors itself, but mislabeling an already-broken SELECT column shifts the error message in ways that make it look like a label problem. Always audit SELECT and GROUP BY before touching LABEL.

COUNT() vs. COUNT(column): COUNT(D) counts non-null values in column D. COUNT() with no argument isn't valid in Google's implementation. As of June 2026, this sometimes surfaces as cannot group without agg rather than a more specific error, which wastes debugging time. If you're seeing the error and the grouping looks right, check your COUNT syntax.

Expressions across groups: SUM(D)/COUNT(D) is valid (both components are aggregated). SUM(D)/D is not (D in the denominator is unaggregated). The rule applies to every term in any expression, not just standalone column references.

ModelMonkey catches this class of QUERY error before it writes to the sheet and suggests the corrected formula directly in the sidebar - useful when you're 6 levels deep in a multi-tab model and the error message alone doesn't identify which column is the offender.