*59*

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