Google Sheets is a versatile tool for organizing, analyzing, and visualizing data. One of the most powerful features is the QUERY command, which allows you to retrieve, filter, and manipulate data using a SQL-like language. In this tutorial, we'll explore the QUERY command in detail and learn how to harness its power using practical examples with a sample table in Google Sheets.

Sample Data

To better understand the examples, let's consider the following table in Google Sheets:

1.  Basic QUERY Command Syntax

1.1 Basic syntax of the QUERY command

=QUERY(data, query, [headers])

• data: The range of cells containing the data you want to query.
• query: A text string containing your query in the Google Visualization API Query Language.
• headers (optional): The number of header rows in your data range. If omitted, Google Sheets will attempt to determine this automatically.

1.2. Simple data retrieval

=QUERY(A1:D6, "SELECT *", 1)
This command returns all rows and columns from the data range A1:D6.

2. Filtering and Sorting Data

2.1 Filtering data with the WHERE clause

To retrieve rows where the Age (column B) is greater than 30:
=QUERY(A1:D6, "SELECT * WHERE B > 30", 1)

2.2. Sorting data with the ORDER BY clause

To sort the results of the previous example by Sales (column D) in descending order:
=QUERY(A1:D6, "SELECT * WHERE B > 30 ORDER BY D DESC", 1)

3. Aggregating Data

3.1 Using aggregate functions

To calculate the average Sales (column D) for rows where the City (column C) is "New York":

=QUERY(A1:D6, "SELECT AVG(D) WHERE C = 'New York'", 0)

3.2 Grouping data with the GROUP BY clause

To calculate the sum of Sales (column D) for each unique City (column C):

=QUERY(A1:D6, "SELECT C, SUM(D) GROUP BY C", 1)

4: Limiting and Offsetting Results

4.1 Limiting the number of results with the LIMIT clause

To return the top 3 rows with the highest Sales (column D):

=QUERY(A1:D6, "SELECT * ORDER BY D DESC LIMIT 3", 1)

4.2 Offsetting results with the OFFSET clause

To return the top 3 rows with the highest Sales (column D) starting from the second-highest:

=QUERY(A1:D6, "SELECT * ORDER BY D DESC LIMIT 3 OFFSET 1", 1)

5. Combining Multiple Clauses

You can combine multiple clauses in a single QUERY command to perform more complex data manipulations. For instance, if you want to find the total Sales (column D) for each unique City (column C) for people older than 30, sorted by the total Sales in descending order:

=QUERY(A1:D6, "SELECT C, SUM(D) WHERE B > 30 GROUP BY C ORDER BY SUM(D) DESC", 1)

Conclusion

The QUERY command in Google Sheets is a powerful and versatile function that can significantly enhance your data analysis and manipulation capabilities. As you become more familiar with the various clauses and syntax, you'll be able to perform complex tasks and retrieve valuable insights from your data with ease. With this comprehensive tutorial and practical examples, you're now equipped to harness the full potential of the QUERY command and improve your overall productivity in Google Sheets.