Google Sheets QUERY: Group Data
GROUP BY
Using GROUP BY with aggregate functions (seen on the previous page), it's possible to obtain grouped results instead of a single result for all data.
To group the data to be returned, add GROUP BY followed by the data to group.
In this test database, each user has a rank (D) ranging from 0 to 2. In the next examples, the users will be grouped by rank.
GROUP BY + COUNT
To get the number of users in each group, enter:
=QUERY(DB!A1:G15,"SELECT COUNT(A) GROUP BY D")
The number of users is well displayed but you still need to add column D after SELECT to know which rank each value corresponds to:
=QUERY(DB!A1:G15,"SELECT D, COUNT(A) GROUP BY D")
GROUP BY + SUM
To get the sum of messages (E) of each group, enter:
=QUERY(DB!A1:G15,"SELECT D, SUM(E) GROUP BY D")
GROUP BY + AVG
To get the average of messages (E) of each group, enter:
=QUERY(DB!A1:G15,"SELECT D, AVG(E) GROUP BY D")
GROUP BY + MAX
To get the largest number of messages (E) of each group, enter:
=QUERY(DB!A1:G15,"SELECT D, MAX(E) GROUP BY D")
Use with multiple functions
It is quite possible to use several aggregate functions in the same query and to add one or more conditions.
For example, to obtain different data for each group ignoring users with 0 messages, enter:
=QUERY(DB!A1:G15,"SELECT D, COUNT(E), SUM(E), AVG(E), MAX(E), MIN(E) WHERE E > 0 GROUP BY D")