The QUERY Function

• Introduction
• Method

Transform, filter and aggregate sets of data using the QUERY function.

Introduction

query does exactly what it says on the tin. More powerful and sophisticated than the filter function, query allows you to execute complex queries to help answer specific questions about your data.

Method

Let us look briefly at the simplified table of data below. It is trival to use the average function to find the arithmetic mean of all the numeric values in column F, as illustrated below. The same outcome could also be achieved using the query function too, albeit with a little more complexity.

AVERAGE(F:F)

INDEX(QUERY(F1:F5, "select avg(F)", 1), 2, 1)

Using query we can also select all the students from the table above who beat the class average, outputting them as a new list.

QUERY(A1:F5, CONCATENATE("select D, F where F > ", INDEX(QUERY(F1:F5, "select avg(F) label avg(F) ''", 1), 2, 1)), 1)

Most powerfully, we can use use a variety of complex query constructs, such as group by, max, min, limit and pivot. It is also fairly trivial to use the function to operate on data sets imported using the importrange function. When using query function on data that is in the same spreadsheet, you have to address the columns using column letters (as shown above). These need to refer to the actual column letters, rather than letters that are relative to the range you are using, e.g. if you are querying range C:F, use the letters C, D, E & F - rather than A-D. When using it on remote / imported sources of data, you need to use the nomenclature of Col1, Col2 etc. These numbers are relative to the range you are importing (as the function won’t have any context of where the source data is positioned within a sheet).