Google Sheets QUERY Group By Totals
By Szabó Gergő · Updated
QUERY group by returns one row per category with an aggregate. Label the sum so the header is readable.
Syntax and arguments
=QUERY(A1:D200,"select B, sum(D) where D is not null group by B label sum(D) 'Total'",1)- category
- The column to group, such as region.
- amount
- The numeric column to sum.
QUERY group totals examples
01
Total by region
Region B, amount D.
=QUERY(A1:D200,"select B, sum(D) where D is not null group by B label sum(D) 'Total'",1)One row per region.
02
Count and sum
Need volume too.
=QUERY(A1:D200,"select B, count(A), sum(D) where B is not null group by B",1)count(A) is the number of rows in the group.
03
Pivot two fields
Region B, status C.
=QUERY(A1:D200,"select B, C, sum(D) where D is not null group by B, C",1)Each region-status pair gets a total.
Common mistakes
Grouping a mixed-type amount
Coerce D to numbers. QUERY will ignore numeric where clauses on a text column.
Forgetting group by after sum()
Every selected non-aggregated column must appear in group by.
Using Excel column letters inside IMPORTRANGE QUERY
Imported ranges use Col1, Col2, not A, B.
QUERY group totals FAQ
Can I order by the total?
Yes. Add order by sum(D) desc.
How do I filter one region?
Put the filter in where before group by: where B = 'East'.
Is this a Pivot Table?
It is a formula pivot. A Pivot Table UI is still better for ad-hoc slicing.