Work with Your Data
Many of us use google sheets on a daily basis. You can get by with a few functions and create a report that summarizes your data set in a nice and clear way. The two most popular functions that help with it are Sumif and Vlookup. In addition, you can create a Pivot table which is quite useful as well.
However, there is another powerful function to add in your arsenal – a Query function. It allows you to send requests to your data by utilizing an SQL-like syntax.
- Type =Query () in your google spreadsheet.
- In the brackets, first select a data set located on any of the tabs.
- Then, within quotation marks, specify which columns to select by using an SQL-like query and a key word SELECT (just like in the example above).
Let’s say the data is stored in a range D2:F14 on a current tab. An example data set has Region, Country and Population. Then type the following to select all the countries located in Asia & Pacific region and their population.
=QUERY(D2:F14,"SELECT E, F WHERE D = 'Asia & Pacific'")
This will result in a list of countries as follows:
To take it one step further and calculate the total population of all the countries that belong to an Asia & Pacific region, type the following:
=QUERY(D2:F14,"SELECT D, sum(F) WHERE D = 'Asia & Pacific' group by D")
The result will show:
To play with the data and create your own query, click here and make a copy of the sheet. To see this post on the web click here.
To learn more about Query function refer to this google doc. The source data for this example is taken from here: country by region, population by country.
Thanks for sharing Dmitry Sorokin, CFA I'm a fan of Google sheet functions that I've seen Dmitry build and simplify the collaboration with multiple stakeholders across multiple sheets. Even functions like sorting results in ascending/descending or larger to smaller values helps with profrssional presentation of analysis.
Hi Aziz M.. There is no join at this point, maybe in future they will add it. However, you can stack the output from two and more queries on top of each other like this { query(), query(), query() } . Also, you can importrange() the data from outside google sheets into your query.
Great stuff, didn't know we could use sql in google sheets. Any more complex queries we can use, like using join? Using outside files?