How To Use the Query() Function

 

 

Summary.

QUERY() is a very powerful function in Google Sheets. If you know SQL, learning QUERY() will be a walk in the park. If not, learning it is also very straightforward. 

Here is the quick rundown: 

  • QUERY(data, query, header) takes 3 Arguments. 
  • The data is the range (i.e. A1:D50)
  • The query argument tells the function what to return and written in quotations (i.e. "SELECT Col1") 
  • The header is optional. This number tells the function how many headers are in the data range provided.
  • All together, here is one example: QUERY(A1:D50, "SELECT Col1, Col2", 1).

What is the Query() Function? 

QUERY() is a versatile function in Google Sheets that allows us to perform complex data manipulations, calculations across a large data range. 

QUERY() has 2 required arguments (data, query) and 1 optional argument (header). 

Here is sample syntax for the function: QUERY(A:D, "SELECT A", 0). 

More on the Data Argument

The data argument is simply the data set that we want to run the query argument against. What this means in Google Sheets is that literally anything can be passed into QUERY() as the data. For instance, we can use QUERY() on top of QUERY() as in this simple example - QUERY(QUERY(A:D, "SELECT A", 1), "SELECT Col1 LIMIT 1"). 

Here the result of QUERY(A:D, "SELECT A", 1) is used as the data for the outer QUERY() function.


More on the Query Argument 

If you are familiar with any version of SQL, you'll come to realize that the query argument can be written in a very similar way but with some minor differences. These differences are noted below. 

  1. The query argument is the second parameter in the function.
  2. You can select the columns based on the regular cell notation (i.e A, B, C, D, ..., AZ, ...) or use the numerical notation (i.e. Col1, Col2, Col3, ...). There's no difference between the two except the latter may be more transferrable; meaning for QUERY(A:D, "SELECT Col1") and QUERY(E:H, "SELECT Col1"), Col1 would represent the A and E columns respectively.
  3. You don't have to use all the Keywords but if you do, they need to be in this order : 
    • SELECT
    • WHERE
    • GROUP BY
    • ORDER BY
    • LIMIT
    • LABEL
  4. You don't have to use all the Aggregation functions but here are the ones most used :
    • SUM
    • AVERAGE
    • MIN
    • MAX
  5. You also don't have to use the advanced QUERY keyword but here are some of them :
    • YEAR
    • DATE
    • MONTH
  6. Lastly, you can use a specific cell out side of the data range in the query argument. Here is one example, QUERY(A:D, "SELECT B, C WHERE D = '"&$G$2&"'",0).


Examples of How We Have Used QUERY()

Using QUERY() with a dropdown. This first example is from our Beige Debt Payoff Dashboard. The QUERY() function sums all interest paid in a given year for a particular loan. The loan can be changed by the loan dropdown and the year can be changed using the year dropdown. Adding this simple feature provides a great way to create interactive dashboards and is super intuitive to use. 

Figure 1. Debt Payoff Dashboard QUERY() Example



This next example demonstrates how we can wrap QUERY() inside other Google Sheets functions from our Networth Dashboard to output the assets amounts by category for the selected year. 
Figure 2. Networth Dashboard QUERY() Example



This final example is the simplest one in terms of syntax. This QUERY() example from out Neutral Roommates/Couples Ultimate Cost Splitter Dashboard is selecting and returning all values from Column O in descending order.
Figure 3. Neutral Roommates/Couples Cost Splitter Dashboard QUERY Example


If you have questions or suggestions, please comment below. In the meantime, Happy Planning!



Comments

Post a Comment