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")
google sheets query function count group by

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")
google sheets query function select group by

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")
google sheets query function sum group by

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")
google sheets query function avg group by

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")
google sheets query function max group by

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")
google sheets query function group by where