Google Sheets QUERY: Aggregate Functions

Aggregate functions allow to get calculated values based on multiple rows of the database.

They can be used for example to calculate the sum of user messages or the number of users in a category.

COUNT

The COUNT function returns the number of rows matching the query (and whose cell of the specified column is not empty).

To get the number of users who have a total number of messages greater than 0, enter:

=QUERY(DB!A1:G15,"SELECT COUNT(A) WHERE E > 0")

google sheets query function count aggregate functions

Or to get the total number of rows in the database, enter:

=QUERY(DB!A1:G15,"SELECT COUNT(A)")

google sheets query function count rows aggregate functions

To not miss a row in the calculation, enter in the COUNT() function any column that does not contain empty cells (as these are ignored by the COUNT function).

SUM

The SUM function calculates the sum of the numbers in a column.

To get the sum of all messages (E), enter:

=QUERY(DB!A1:G15,"SELECT SUM(E)")

google sheets query function select sum aggregate functions

AVG (average)

The AVG function calculates the average of the numbers in a column.

To get the average of all messages (E) from users whose rank (D) is 1, enter:

=QUERY(DB!A1:G15,"SELECT AVG(E) WHERE D = 1")

google sheets query function avg average aggregate functions

MIN and MAX

The MIN and MAX functions return the minimum or maximum value of a column.

To get the largest number of messages (E) from all users, enter:

=QUERY(DB!A1:G15,"SELECT MAX(E)")

google sheets query function select max aggregate functions