How to Use QUERY to Group Data in Google Sheets?
Group By with QUERY is a Google Sheets function that groups rows by the first column and sums the values in the second column for each group.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Grouping data by category and summing values is crucial for analysis. The QUERY function simplifies this process, similar to SQL's GROUP BY.
The Formula
"Use QUERY to group rows by category and sum a numeric column for each group, like SQL GROUP BY"
=QUERY(A1:C, "SELECT A, SUM(B) GROUP BY A", 1)This formula groups rows by the first column and sums the values in the second column for each group.
Step-by-Step Breakdown
- A1:C specifies the range of data to query.
- SELECT A indicates that we want to group by the values in column A.
- SUM(B) calculates the total of the values in column B for each group.
- GROUP BY A groups the results based on the unique values in column A.
Edge Cases & Warnings
- Empty rows in the data range may lead to unexpected results.
- Non-numeric values in the summation column will cause errors.
- If there are no matching groups, the output will be empty.
- Using merged cells in the range can disrupt the grouping process.
Examples
"Categories: A, B, A, B; Values: 10, 20, 30, 40"
A: 40, B: 60"Categories: X, Y, X; Values: 5, 15, 10"
X: 15, Y: 15Frequently Asked Questions
Can I group by multiple columns?
Yes, you can add more columns in the GROUP BY clause.
What happens if I include a non-numeric column in SUM?
It will result in an error since SUM only works with numeric values.
Is there a limit to the number of groups?
No, but performance may degrade with very large datasets.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.