# How To Use Google Sheets QUERY SELECT & WHERE With Examples

Are you looking to learn how to use the Google Sheets ``` QUERY ``` function to select and filter data based on specific conditions?

In this blog post, you’ll explore the ins and outs of the powerful ``` QUERY ``` function, particularly focusing on ``` SELECT ``` and ``` WHERE ``` clauses, to help you get the most out of your Google Sheets experience.

In the following sections, I’ll start with an introduction to Google Sheets and its basic functions to ensure you’ve got a solid foundation. From there, I’ll dive into the ``` QUERY ``` function, examining its syntax, purpose, and the integral role of ``` SELECT ``` and ``` WHERE ``` clauses within the function.

As I progress through the material, I’ll offer practical examples of how to combine ``` SELECT ``` and ``` WHERE ``` clauses effectively, and explore advanced use cases of ``` QUERY ``` for an even greater level of data management. Finally, I’ve included some helpful tips and tricks to address common pitfalls and improve ``` QUERY ``` function performance, as well as links to further resources for continued development in your Google Sheets journey.

## Getting Started with Google Sheets

### An introduction to basic Google Sheets functions

Google Sheets allows you to perform a variety of tasks and calculations using built-in functions. Some of the basic functions to get you started are:

• SUM: Adds the specified numbers or cell references. For example, ``` =SUM(B2:B5) ``` calculates the sum of the values in cells B2 to B5.
• AVERAGE: Calculates the average of the given numbers or cell references. For example, ``` =AVERAGE(C2:C7) ``` calculates the average of the values in cells C2 to C7.
• MAX: Returns the highest number from the specified numbers or cell references. For example, ``` =MAX(D3:D8) ``` returns the highest value within cells D3 to D8.
• MIN: Returns the lowest number from the specified numbers or cell references. For example, ``` =MIN(E2:E10) ``` returns the lowest value within cells E2 to E10.

Google Sheets offers a vast array of functions that cater to various needs. These basic functions are just the beginning of the powerful capabilities of this online spreadsheet tool.

## Understanding the ``` QUERY ``` Function

The ``` QUERY ``` function in Google Sheets is a powerful tool that allows you to manipulate and analyse your data. It uses the capabilities of the Structured Query Language (SQL) to provide an easy-to-use method for filtering, sorting, and even performing calculations on your data. In essence, it’s like having a database at your fingertips, without any additional tools or software required.

### Syntax of the QUERY function

The syntax of the ``` QUERY ``` function is quite simple and easy to follow, even for beginners. Here’s the complete structure:

``=QUERY(data_range, query_string, [headers])``

Where:

• data_range: The range of cells you want to perform the query on.
• query_string: The SQL-like query you want to perform, enclosed in double quotes.
• headers (optional): The number of header rows to consider in the data_range.

### Components of a QUERY function (SELECT, WHERE, etc.)

``` QUERY ``` function uses a number of SQL components, which include:

• SELECT: Specifies the columns you want to display as a result of your query. If you want to display multiple columns, separate them with a comma.
• WHERE: Allows you to filter rows based on specific criteria. Commonly used with conditional operators, such as equals, not equals, less than, greater than, etc.
• GROUP BY: Organises results into groups based on the values in specified columns. This is particularly helpful when working with aggregate functions.
• ORDER BY: Sorts the results based on the values in a specified column. You can choose to sort in ascending or descending order.
• LIMIT: Limits the number of rows returned by the query.

## Diving into ``` SELECT ``` Statements

### Use of ``` SELECT ``` in ``` QUERY ``` function

In the ``` QUERY ``` function, the ``` SELECT ``` statement serves as a crucial component, as it allows you to choose the specific columns you’d like to retrieve from your data. If you want to display particular columns instead of the entire dataset, using ``` SELECT ``` will enable you to focus on the information that matters most to you.

### ``` SELECT ``` Specific Columns

To select a specific column from your data, use the column letter (e.g., A, B, C) or the column name (if you have headers) in your ``` SELECT ``` statement. For example, you can use ``` =QUERY(A1:C10, "SELECT A") ``` to display only the values from the column A of your data range.

### ``` SELECT ``` Multiple Columns

If you’d like to display multiple columns instead of just one, you can do that by including a comma-separated list of the column letters or names within the ``` SELECT ``` statement.

For example, ``` =QUERY(A1:C10, "SELECT A, B") ``` would show values from columns A and B of the specified data range.

Or if you want to use all the columns in the selected range you can apply the all syntax which is an asterisk ``` * ``` .

For example, ``` =QUERY(A1:C10, "SELECT *") ``` which would show all the values from columns A, B and C of the specified data range.

### Aggregating Results Using ``` SELECT ```

Advanced formatting of your query results can be achieved by applying various functions within the ``` SELECT ``` statement.

For example, ``` COUNT ``` to obtain the count of specific values, or ``` SUM ``` to add up numeric values.

Functions like these expand the flexibility of your queries, giving you greater insight into your dataset.

## Mastering the ``` WHERE ``` Clause

In the ``` QUERY ``` function, the ``` WHERE ``` clause is essential to filtering data based on specific conditions. It helps narrow down the results to include only rows that meet the criteria, making it easier to find relevant information.

### Simple Conditions (Equals, Not Equals)

Simple conditions involve using equal to ``` = ``` and not equal to ``` <> ``` operators to filter data.

For example, ``` SELECT * WHERE Col1 = 'John'" ``` will give you rows containing ``` 'John' ``` in the first column.

Similarly, ``` "WHERE Col1 <> 'John'" ``` will return rows that don’t have ``` 'John' ``` in the first column.

### Complex Conditions ( ``` AND ``` , ``` OR ``` )

Complex conditions include using ``` AND ``` and ``` OR ``` operators to filter data based on multiple criteria. You can use the ``` AND ``` operator to ensure that all specified conditions are met, while the ``` OR ``` operator requires only one of the stipulated conditions to be satisfied.

For instance, ``` "SELECT * WHERE Col1 = 'John' AND Col2 = 'Doe'" ``` will return rows where both conditions are true, and ``` "SELECT * WHERE Col1 = 'John' OR Col2 = 'Doe'" ``` will give you rows where either condition is accurate.

### Conditional Operators (<, >, <=, >=)

Conditional operators ``` '<', '>', '<=', '>=' ``` filter data based on numerical, date, or time values, allowing you to narrow down results within a specific range.

For example, ``` "SELECT * WHERE Col1 > 10" ``` will show rows where the value in the first column is greater than ``` 10 ``` , and ``` "SELECT * WHERE Col2 <= '2020-01-01'" ``` will include rows where the date in the second column is on or before ``` 1st January 2020 ``` .

### Text Conditions (Starts With, Contains, etc.)

Text conditions are useful for exploring data based on partial information. You can utilise the ``` 'starts with' ``` , ``` 'contains' ``` , and ``` 'ends with' ``` conditions to find rows that meet specific text criteria.

For instance, using ``` "SELECT * WHERE Col1 STARTS WITH 'A'" ``` will return rows where the first column begins with the letter ‘A’, while ``` "SELECT * WHERE Col1 CONTAINS 'John'" ``` will provide rows where the first column contains the word ‘John’.

### Practical Examples of Combining SELECT and WHERE

Let’s say you have a dataset containing information like Name, Age, Country, and Occupation.

You can use a combined ``` QUERY ``` like this:

``=QUERY(A1:D25,"SELECT B, C WHERE D='Australia'")``

This ``` QUERY ``` will display a table with both the Age and Country columns, but only include rows where the Occupation column matches ‘Australia’.

In another example, assume you want to find all records of customers aged 25 or above who live in Australia. You can use the following QUERY:

``=QUERY(A1:D25, "SELECT A, B, C WHERE B >= 25 AND C = 'Australia'")``

This QUERY will return the Name, Age, and Country columns for records meeting the specified age and location criteria.

## Summary Working with ``` QUERY ``` , ``` SELECT ``` & ``` WHERE ```

After going through this blog post, you should now have a solid understanding of how to use the Google Sheets ``` QUERY ``` function with ``` SELECT ``` and ``` WHERE ``` clauses. Familiarising yourself with these powerful tools will allow you to perform advanced data analysis and manipulation with ease. As you continue to work with Google Sheets, you’ll also discover various other handy functions that can further improve your productivity and efficiency when handling data.

### Recap of what’s been covered in the blog post

This post has covered the basic fundamentals of the ``` QUERY ``` function in Google Sheets, focusing primarily on the ``` SELECT ``` and ``` WHERE ``` clauses. You’ve learnt how to create a new Google Sheet, use basic functions, build and format SELECT statements, and master the ``` WHERE ``` clause.

In this blog post, you’ve explored the powerful capabilities of the ``` QUERY ``` function in Google Sheets. Designed as a comprehensive introduction for beginners, we’ve delved into the essential components of the function, providing detailed explanations, practical examples, and helpful tips for mastering the ``` QUERY ``` function. By understanding how to effectively use ``` SELECT ``` and ``` WHERE ``` in your queries, you’ll be able to retrieve and display specific data that meets your desired criteria, making your Google Sheet experience more efficient and customisable.

For further examples and insights on Google Sheets ``` QUERY ``` formula with multiple criteria go here .

Ryan Sheehy
Ryan has been dabbling in code since the late '90s when he cut his teeth exploring VBA in Excel. Having his eyes opened with the potential of automating repetitive tasks, he expanded to Python and then moved over to scripting languages such as HTML, CSS, Javascript and PHP.