You can use the following syntax to group and aggregate data in a Google Sheets Query:
=query(A1:D12, "select B, avg(D) group by B", 1)
In this example, we select columns B and D in cell range A1:D12. We then find the average of column D, grouped by column B. We also specify a 1 to indicate that there is 1 header row at the top of the dataset.
In this example, we used the avg() aggregate function, but we can use any of the following aggregate functions:
- avg()
- sum()
- count()
- min()
- max()
The following examples show how to group and aggregate data in practice.
Example 1: Group & Aggregate by One Column
We can use the following formula to select the Team and Points columns, then find the average of the Points column, grouped by Team:
This tells us that:
- The average points scored by players on the Cavs is 10.8.
- The average points scored by players on the Celtics is 12.7.
And so on.
Example 2: Group & Aggregate by Multiple Columns
We can use the following formula to select the Team, Conference and Points columns, then find the maximum of the Points column, grouped by Team and Conference:
This tells us that:
- The max points scored by any player on the Cavs team in the East Conference is 13.4.
- The max points scored by any player on the Celtics team in the East Conference is 22.4.
And so on.
How to Avoid #VALUE! Errors
To avoid #VALUE! errors when grouping and aggregating data, make sure that every column included in the select statement meets one of the following criteria:
- Has an aggregate function applied to it.
- Is included in the group by statement.
As long as each column in the select statement meets one of these criteria, then you can avoid a #VALUE! error.
Additional Resources
Google Sheets Query: How to Select Multiple Columns
Google Sheets Query: Select Rows that Contain String
Google Sheets Query: How to Use Order By