Google Sheets | QUERY | Function | GROUP BY | Clause | How To | Example | Spreadsheet | Tutorial

Use the Google Sheets QUERY function and GROUP BY clause to group data by a column which has repetitive data using one or more of the following aggregate functions: avg, count, sum, max, and min.
Here's a scenario where using GROUP BY would be ideal: Consider a workshop registrants database. It has data like registration date, registrant name, city, payment mode, and food preference, which are vegetarian or non-vegetarian. Assume you want to find out the total number of registrants who prefer one of the two meal types. The QUERY function with GROUP BY clause can be used to extract this data.
For an intro to QUERY function, please refer to the tutorial How to Use Query in Google Sheets whose link is given below.
-------------------------------------
How to Use QUERY in Google Sheets?
QUERY helps, among others, to extract specific or all data from a range:
• Google Sheets | QUERY ...
-------------------------------------
How to Use QUERY with WHERE clause and MATCHES operator in Google Sheets?
MATCHES operator assists to conduct an exact or a wildcard search:
• Google Sheets | QUERY ...
-------------------------------------
How to Use QUERY with WHERE clause and starts with or ends with operators in Google Sheets?
Use where clause and starts with, ends with or both the operators to get data based on one or more conditions:
• Google Sheets | QUERY ...
-------------------------------------
How to Use QUERY with WHERE clause in Google Sheets?
To conditionally extract data, use the QUERY function with its WHERE CLAUSE:
• Google Sheets | QUERY ...
-------------------------------------
How to Use XLOOKUP to Extract Multiple Values in Google Sheets?
XLOOKUP can return a single row or column with the search key:
• Google Sheets | XLOOKU...
-------------------------------------
How to Use VLOOKUP in Google Sheets?
Use VLOOKUP to get a single value:
• VLOOKUP Google Sheets ...
-------------------------------------
How to Use IFS in Google Sheets?
IFS allow multiple logical expressions:
• Google Sheets IFS | Te...
-------------------------------------
How to Use IF in Google Sheets?
Use IF to compare one value with another:
• Google Sheets IF | Tut...
-------------------------------------
How to Use IF with AND in Google Sheets?
Use IF with AND to Use Multiple Logical Expressions in IF:
• Google Sheets | IF | A...
-------------------------------------
How to Create a Pivot Table in Google Sheets?
Create a pivot table for calculation and in-depth data analysis:
• Google Sheets | Pivot ...
-------------------------------------
Which one to Use? A Pivot Table, or COUNTBLANK Function, to Count Blank Cells in Google Sheets:
Gives steps to use a pivot table or COUNTBLANK to count blank cells:
• Google Sheets | Pivot ...
-------------------------------------
Key Points: 1. The column(s) that is included in the SELECT clause should also be included in the GROUP BY clause 2. An aggregate function should be used on the column(s) that will not be included in GROUP BY.
Here's the format of the QUERY function formula:
=QUERY(data, query, [headers])
Use the GROUP BY clause in the query part of the formula.
Let's look at an example:
Example
Assume E2 to E280 has food preference data of a workshop registrants. E2 is labelled as Food Preference.
Say you want to get the total count of people who have opted for vegetarian meals and of those who have opted for non-vegetarian meals.
The QUERY function formula with GROUP BY clause is:
=QUERY(E2:E280, "select E, count(E) group by E", 1)
If there are 24 people who have opted for vegetarian, and 256 for non-vegetarian meals, the output of the formula will be:
Food Preference count Food Preference
Non-vegetarian 256
Vegetarian 24
Review this video tutorial, which gives the step to use the Google Sheets
QUERY function with the GROUP BY clause, with examples.