Cindy Sheets and Reach Out and Read
Take yous heard of the Google Sheets Query function?
It's the virtually useful role in Google Sheets.
It allows you to employ information commands to manipulate your data in Google Sheets, and information technology'southward incredibly versatile and powerful.
This single-part does the job of many other functions and can replicate most of the functionality of pivot tables.
This video is lesson 14 of thirty from my gratis Google Sheets grade: Avant-garde Formulas 30 Day Claiming
Google Sheets QUERY Function Syntax
=QUERY(data, query, [headers])
It takes iii arguments:
- the range of data you desire to analyze
- the query you want to run, enclosed in quotations
- an optional number to say how many header rows there are in your data
Here's an example QUERY office:
=QUERY(A1:D234,"SELECT B, D",one)
The data range in this case is A1:D234
The query argument is the string inside the quotes, in dark-green. In this case, it tells the function to select columns B and D from the data.
The third argument is the number 1, which tells the function that the original information had a single header row. This argument is optional and, if omitted, will exist determined automatically by Sheets.
It's one of the Google-but functions that are not available in other spreadsheet tools.
QUERY Part Notes
The keywords are non case sensitive, and then you tin can write "SELECT" or "select" and both piece of work.
Nevertheless, the column messages must be capital: A, B, C, etc. otherwise yous'll go an error.
The keywords must appear in this order (of course, you lot don't have to use them all):
- select
- where
- group past
- order past
- limit
- label
Y'all'll come across examples of all of these keywords below.
There are a few other keywords but they are much less common. Run into the full list here.
Google Sheets QUERY Function Template
Click here to open a view-only re-create >>
Experience free to make a re-create: File > Make a copy…
If you can't access the template, it might be because of your organization's Google Workspace settings. If yous right-click the link and open up it in an Incognito window you'll be able to run across information technology.
Google Sheets QUERY Function Examples
Set Up
If you want to follow along with the solutions, please make a copy of the Google Canvass template higher up.
This is what our starting information looks similar:
In this tutorial, I have used a named range to identify the information, which makes it much easier and cleaner to use in the QUERY office. Feel free to utilise the named range "countries" too, which already exists in the template.
If y'all're new to named ranges, hither'due south how you create them:
Select your data range and go to the carte du jour:
Data > Named ranges…
A new pane will show on the right side of your spreadsheet. In the first input box, enter a name for your table of information so y'all tin refer to information technology hands.
SELECT All
The statement SELECT * retrieves all of the columns from our data table.
To the right side of the table (I've used cell G1) type the following Google Sheets QUERY role using the named range notation:
=QUERY(countries,"SELECT *",1)
Notes: if you lot don't want to use named ranges then that's no trouble. Your QUERY formula volition look similar this:
=QUERY(A1:D234,"SELECT *",ane)
For the remainder of this article, I've used the named range "countries" only experience complimentary to continue using the regular range reference A1:D234 in its place.
The output from this query is our total table once again, considering SELECT * retrieves all of the columns from the countries tabular array:
Wow, there you get! You've written your get-go QUERY! Pat yourself on the dorsum.
SELECT Specific Columns
What if we don't want to select every cavalcade, but only certain ones?
Modify your Google Sheets QUERY office to read:
=QUERY(countries,"SELECT B, D",1)
This fourth dimension we've selected only columns B and D from the original dataset, and so our output will wait similar this:
Important Note
Remember, our QUERY function is in cell G1, and so the output volition be in columns Yard, H, I, etc.
The B and D inside the QUERY select statement refer to the column references back in the original data.
WHERE Keyword
The WHERE keyword specifies a condition that must be satisfied. It filters our data. Information technology comes later the SELECT keyword.
Change your Google Sheets QUERY office to select but countries that have a population greater than 100 1000000:
=QUERY(countries,"SELECT B, D WHERE D > 100000000",1)
Our output table is:
Allow'south meet some other WHERE keyword instance, this time selecting only European countries. Modify your formula to:
=QUERY(countries,"SELECT B, C, D WHERE C = 'Europe' ",1)
Notice how there are single quotes around the word 'Europe'. Contrast this to the numeric instance before which did non crave single quotes around the number.
Now the output table is:
ORDER Past Keyword
The Guild Past keyword sorts our data. Nosotros can specify the column(s) and direction (ascending or descending). It comes after the SELECT and WHERE keywords.
Let's sort our data by population from smallest to largest. Modify your formula to add the following Society By keyword, specifying an ascending direction with ASC:
=QUERY(countries,"SELECT B, C, D ORDER BY D ASC",one)
The output table:
Modify your QUERY formula to sort the data by land in descending guild, Z – A:
=QUERY(countries,"SELECT B, C, D ORDER Past B DESC",i)
Output table:
LIMIT Keyword
The LIMIT keyword restricts the number of results returned. It comes after the SELECT, WHERE, and ORDER Past keywords.
Let's add together a LIMIT keyword to our formula and return only x results:
=QUERY(countries,"SELECT B, C, D ORDER BY D ASC LIMIT x",1)
This now returns only 10 results from our data:
Arithmetic Functions
Nosotros can perform standard math operations on numeric columns.
So let'due south figure out what per centum of the total world population (vii.sixteen billion) each state accounts for.
We're going to divide the population column by the full (vii,162,119,434) and multiply by 100 to calculate percentages. And so, change our formula to read:
=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100",i)
I've divided the values in column D by the total population (inside the parentheses), and then multiplied by 100 to become a percentage.
The output table this fourth dimension is:
Notation – I've applied formatting to the output column in Google Sheets to only show 2 decimal places.
Label Keyword
That heading for the arithmetics cavalcade is pretty ugly right? Well, we can rename it using the LABEL keyword, which comes at the terminate of the QUERY statement. Try this out:
=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100 Label (D / 7162119434) * 100 'Percentage'",ane)
=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100 Characterization (D / 7162119434) * 100 'Percentage' ",1)
Aggregation Functions
We tin can use other functions in our calculations, for example, min, max, and average.
To calculate the min, max and average populations in your state dataset, use aggregate functions in your query as follows:
=QUERY(countries,"SELECT max(D), min(D), avg(D)",1)
The output returns three values – the max, min and average populations of the dataset, equally follows:
Group By Keyword
Ok, take a deep breath. This is the about challenging concept to sympathize. However, if you've ever used pivot tables in Google Sheets (or Excel) and so yous should be fine with this.
The GROUP By keyword is used with amass functions to summarize data into groups as a pin table does.
Permit'southward summarize past continent and count out how many countries per continent. Change your query formula to include a Grouping BY keyword and employ the COUNT amass function to count how many countries, equally follows:
=QUERY(countries,"SELECT C, count(B) GROUP BY C",1)
Note, every cavalcade in the SELECT statement (i.e. before the Grouping Past) must either be aggregated (e.g. counted, min, max) or appear after the GROUP BY keyword (e.g. column C in this case).
The output for this query is:
Let's see a more complex example, incorporating many different types of keyword. Modify the formula to read:
=QUERY(countries,"SELECT C, count(B), min(D), max(D), avg(D) GROUP Past C ORDER Past avg(D) DESC LIMIT 3",one)
This may exist easier to read cleaved out onto multiple lines:
=QUERY(countries,
"SELECT C, count(B), min(D), max(D), avg(D)
Group By C
ORDER BY avg(D) DESC
LIMIT 3",1)
This summarizes our data for each continent, sorts by highest to the lowest boilerplate population, and finally limits the results to just the top 3.
The output of this query is:
Advanced Google Sheets QUERY Office Techniques
How to add together a total row to your Query formulas
How to apply dates as filters in your Query formulas
There are 4 more keywords that haven't been covered in this article: Pivot, Outset, FORMAT and OPTIONS.
In addition, there are more data manipulation functions available than we've discussed higher up. For case, there are a range of scalar functions for working with dates.
Suppose you take a cavalcade of dates in cavalcade A of your dataset, and yous want to summarize your data by twelvemonth. Yous tin ringlet it upwardly by using the YEAR scalar part:
=QUERY(data,"select Yr(A), COUNT(A) group past Year(A)",1)
For more advanced techniques with the QUERY role, have a watch of this lesson from the Advanced 30 course:
This video is lesson 15 of 30 from my gratuitous Google Sheets grade: Advanced Formulas thirty Mean solar day Challenge .
Other Resources
The QUERY Role is covered in days 14 and fifteen of my free Avant-garde Formulas course for Google Sheets: Learn 30 Avant-garde Formulas in thirty Days
Official Google documentation for the QUERY() function.
Official documentation for Google's Visualization API Query Language.
Looking for a Google Sheets expert to help with your next projection? Schedule a consult today with a Ben-approved Google Sheets expert.
Source: https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/
0 Response to "Cindy Sheets and Reach Out and Read"
Post a Comment