Table of Contents
- PURPOSE OF WHERE QUERY CLAUSE IN GOOGLE QUERY LANGUAGE
- EXAMPLE DATA:
- EXAMPLE 1 : DISPLAY THE LIST OF EMPLOYEES WHO HAS A “STAR” BADGE
- EXAMPLE 2: FIND OUT THE FIELD WISE AVERAGE EXPERIENCE OF ALL THE EMPLOYEES HAVING THE STAR BADGE
- DIFFERENT EXPRESSIONS VALID IN WHERE CLAUSE IN GOOGLE QUERY LANGUAGE
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.
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.
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.
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
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.
- Press Enter.
- The result will appear as shown in the picture below.
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.
- Press ENTER.
- The result will appear as shown in the picture below.
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.
|SELECT X WHERE Y>34
|SELECT X WHERE Y <35
|GREATER OR EQUAL TO
|SELECT X WHERE Y >= 76
|LESSER OR EQUAL TO
|SELECT X WHERE Y <=75
|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 NULL
|SELECT X WHERE Y is null
|NOT EMPTY OR NOT NULL
|is not null
|SELECT X WHERE Y is not null
|JOIN THE CONDITION- TRUE if both conditions are TRUE
|SELECT X WHERE Y>45 and Y<60
|JOIN THE CONDITIONS – TRUE if any of the condition is TRUE
|SELECT X WHERE Y =’hello’ or Y=’bye’
|OPPOSITE OF THE CONDITION
|SELECT 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.
|SEARCH TEXT WITH THE SIMILAR STARTING OR SAME PREFIXES
|WHERE X STARTS WITH
|SELECT X WHERE X STARTS WITH ‘HEL’
|SEARCH TEXT WITH SIMILAR ENDINGS OR SAME SUFFIXES
|WHERE X ENDS WITH
|SELECT X WHERE X ENDS WITH ‘LY’
|WHERE X CONTAINS ‘……’
|SELECT X WHERE X CONTAINS ‘HELLO’
|MATCHING A PATTERN OF LETTERS
|WHERE X LIKE ‘CA%’
|SELECT X WHERE X LIKE ‘CA%’ will search cat, cake etc.
|MATCHING EXACT NUMBER OF LETTERS
|WHERE X LIKE ‘_AT’
|SELECT X WHERE X LIKE ‘_AT’ matches CAT, MAT,BAT etc.
|MATCH THE SUB STRING IN A SENTENCE
|WHERE 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.