HOW TO USE WHERE QUERY CLAUSE IN GOOGLE SHEETS ?

Table of Contents

INTRODUCTION

GOOGLE SHEETS is the spreadsheet application offered by Google.

There are many useful functions which are found in almost all the spreadsheet apps like Google Sheets or Microsoft Excel or Open Calc and so on but every application has a certain special portion which is unique from the others.

One of such features in GOOGLE SHEETS is the use of the QUERY FUNCTION which enables us to use the SQL SERVER kind of queries on our data.

This QUERY LANGUAGE IS KNOWN AS THE GOOGLE VISUALIZATION API QUERY LANGUAGE and is almost similar to the SQL query language.

Out of the most important query clauses WHERE is the one which is going to be used a lot.

THE WHERE CLAUSE HELPS US TO RETURN THE ROWS WHICH SATISFIES A GIVEN CONDITION.

We’ll extract the data from a given table which will be based on a given condition with the help of this clause.

In this article , we are going to learn about the WHERE CLAUSE of the GOOGLE SHEETS QUERY LANGUAGE.



PURPOSE OF WHERE QUERY CLAUSE IN GOOGLE QUERY LANGUAGE

Every clause in Google Sheets query language or Google query language or Google visualization api query language has a specific task to do.

WHERE CLAUSE is one of the very important clause which will be used a lot.

This clause is used where we want to extract the data to

  • satisfy a given condition like EQUATING ANY VALUE.
  • match any similar content.
  • match any word with a pattern.
  • Matching prefix or suffix in a given word.
  • extracting data with wildcards.

In such cases we can use the clause WHERE query in GOOGLE SHEETS QUERY LANGUAGE is very helpful.

THE WHERE CLAUSE WILL SIMPLY RETURN THE ROWS WHERE THE GIVEN CONDITION AGREES TO THE GIVEN CONDITION.

The usage of the WHERE CLAUSE is very simple. We’ll take a few example to understand this later in the article.

Let us try to understand this in a simpler way.

Suppose we have a table with data about the employees.

The data can consist of text or numbers.

Let us take an example to understand the concept.

We’ll search different values by posing the situations or conditions for the data.

EXAMPLE DATA

In this data, we can find out the different combinations of data such as all the employees with A CERTAIN BADGE, or the employees having the certain experience, or the department and so on.

We’ll check it out in the examples discussed below.



EXAMPLES:

EXAMPLE DATA:

Let us take a random data and try out our newly learned clause – WHERE.

The example shows a table containing the employee details such as EMPLOYEE ID, AGE , EXPERIENCE and WORK FIELD.

DATA FOR WHERE QUERY EXAMPLES

We’ll take a few examples to get the data from the above table in different ways to understand the WHERE QUERY.



EXAMPLE 1 : DISPLAY THE LIST OF EMPLOYEES WHO HAS A “STAR” BADGE

SOLUTION:

We’ll find out the list of the employees who have got a STAR BADGE.

Now, this is a case where we need to simply filter out the data and get the complete output.

This is an apt example to use the WHERE QUERY in which we ‘ll put the condition and get the output.

FOLLOW THE STEPS TO CREATE A TABLE WITH THE LIST OF EMPLOYEES WHO HAVE THE STAR BADGE

  • Select the cell where you want to get the result by double clicking it.
  • Enter the function as =QUERY(B6:F19,”SELECT B,C,D,E,F WHERE C=’STAR'”,-1).
QUERY EXPLANATION:
The QUERY used is =QUERY(B6:F19,"SELECT B,C,D,E,F WHERE C='STAR'",-1).
Select will populate the selected selected column. We want all the columns so we mentioned all the columns B,C,D,E and F.
WHERE will allow us to put the condition which we used by EQUATING THE TEXT STAR.
The last argument is -1 which will take the HEADER ROW in automatic.
ENTER THE FUNCTION AS SHOWN IN THE PICTURE
  • Press Enter.
  • The result will appear as shown in the picture below.
RESULT OF THE FUNCTION. THE DATA IS CREATED AS WE REQUIRED

We can see that with the help of a single statement , we could get a complete table with the desired format.

We could get the complete data which satisfies the given condition.

The result shows the Employees which have the Badge STAR.

The output shows all the columns which we put in SELECT.



EXAMPLE 2: FIND OUT THE FIELD WISE AVERAGE EXPERIENCE OF ALL THE EMPLOYEES HAVING THE STAR BADGE

Let us take another example and add a few more conditions to the statement.

The prettiest thing about the Queries is that it help us to get the data as we want without affecting the original data.

FOLLOW THE STEPS TO GET THE FIELD WISE AVERAGE EXPERIENCE OF THE STAR-BADGE EMPLOYEES

  • Select the cell where you want the result.
  • Enter the formula as

=QUERY(B6:F19,”SELECT AVG(E),COUNT(E),F WHERE C=’STAR’ GROUP BY F”,-1)

The Query used is 

=QUERY(B6:F19,"SELECT AVG(E),COUNT(E),F WHERE C='STAR' GROUP BY F",-1)
The first argument is the DATA LIMITS which contain the data from which we'd extract the results.
The second argument is the QUERY.
Select will enlist all the columns listed. AVG(E) will get the Average of the Column E i.e. Experience. Count (E) will get the count of the values in column E, F will enlist F column i.e. Field.
WHERE C='STAR' will put the condition on the complete query that the operation would take place on the rows only where C column = STAR.
It simply means the count and average will be done to the only values satisfying the WHERE CONDITION.
GROUP BY F will group all the values through the distinct values of the column i.e. the different FIELDS.
The number of headers is shown by -1  for automatic. We can also use 1 for this example.
ENTERING THE FUNCTION TO PIVOT FIELD AS WE REQUIRE
  • Press ENTER.
  • The result will appear as shown in the picture below.



RESULT. THE EMPLOYEES SHOWING THE AVERAGE EXPERIENCE OF THE DIFFERENT EMPLOYEES FIELD-WISE

THE RESULT IS A COMPLETE TABLE WITH THE EMPLOYEES HAVING THE STAR BADGE AND SHOWING THE AVERAGE EXPERIENCE IN THE DIFFERENT FIELDS.

THE OUTPUT FORMAT NEEDS TO BE MANUALLY SET. THE FORMAT WILL DEPEND UPON THE FORMAT OF THE CELL.



DIFFERENT EXPRESSIONS VALID IN WHERE CLAUSE IN GOOGLE QUERY LANGUAGE

As we learnt the use of WHERE CLAUSE in GOOGLE QUERY LANGUAGE, it is important to know the different expressions which we can use.

We are creating a reference table here to let you through the expression and its effect on the data.

EFFECTEXPRESSION/OPERATOREXAMPLE
GREATER THAN>SELECT X WHERE Y>34
LESS THAN<SELECT X WHERE Y <35
GREATER OR EQUAL TO>=SELECT X WHERE Y >= 76
LESSER OR EQUAL TO<=SELECT X WHERE Y <=75
EQUAL TO=SELECT X WHERE Y=23, SELECT X WHERE Y=’HELLO’
NOT EQUAL TO!=SELECT X WHERE Y!=20, SELECT X WHERE Y!=’BYE’
NOT EQUAL TO<>SELECT X WHERE Y<>30, SELECT X WHERE Y<>’BYE’
EMPTY OR NULLis nullSELECT X WHERE Y is null
NOT EMPTY OR NOT NULLis not nullSELECT X WHERE Y is not null
JOIN THE CONDITION- TRUE if both conditions are TRUEandSELECT X WHERE Y>45 and Y<60
JOIN THE CONDITIONS – TRUE if any of the condition is TRUEorSELECT X WHERE Y =’hello’ or Y=’bye’
OPPOSITE OF THE CONDITIONnotSELECT X WHERE not(Y>56)

In addition to this, we have very powerful options to play with the Strings.

The following table discusses the usage of String options.

EFFECTEXPRESSIONEXAMPLE
SEARCH TEXT WITH THE SIMILAR STARTING OR SAME PREFIXESWHERE X STARTS WITHSELECT X WHERE X STARTS WITH ‘HEL’
SEARCH TEXT WITH SIMILAR ENDINGS OR SAME SUFFIXESWHERE X ENDS WITH SELECT X WHERE X ENDS WITH ‘LY’
WORD MATCHWHERE X CONTAINS ‘……’SELECT X WHERE X CONTAINS ‘HELLO’
MATCHING A PATTERN OF LETTERSWHERE X LIKE ‘CA%’SELECT X WHERE X LIKE ‘CA%’ will search cat, cake etc.
MATCHING EXACT NUMBER OF LETTERSWHERE X LIKE ‘_AT’SELECT X WHERE X LIKE ‘_AT’ matches CAT, MAT,BAT etc.
MATCH THE SUB STRING IN A SENTENCEWHERE X MATCHES ‘CAT’SELECT X WHERE X MATCHES ‘CAT’

So, we saw the samples of various WHERE CLAUSE USAGES.

There are separate articles for some of the frequently used WHERE QUERY CONDITIONS.