Google Sheets - Learn to use QUERY and that's it!
The QUERY function allows you to use your Google Sheet as if it were a real database, querying it with a syntax very similar to that of SQL.
Knowing how to use this feature will be very useful to you as it alone can do the work of many other existing functions as well as replicate functionality such as that of pivot tables.
But let's see immediately how the QUERY function is used in action.
The syntax to be used is to enter the range of cells to be queried and the query to be performed to obtain the result. The last parameter, optional, is used to indicate how many header lines to show above the data, for example 1, otherwise if omitted it will be like setting it to FALSE or -1.
The header must be contained in the range of cells to be queried.
For example, let's start with a table where the ranking of the number of inhabitants for each continent divided by country is shown. The data goes from cell A2 (including the header) to cell D234.
If I wanted to isolate the TOP10 countries by population number I could make a query like this:
=QUERY(A2:D234, "SELECT * LIMIT 10", 1)
That means: from the cells of the selected range, it selects all the values (the asterisk stands for 'all', all) and limits the result to the first 10 rows, it also shows the table header.
If you are familiar with SQL commands you will have noticed that the FROM clause is not defined to indicate the table from which to extract data. This is a particular difference with the syntax from actual SQL, since the table to query, i.e. the data range, is defined in the first parameter of the function.
A curiosity is that, instead of using a range of cells with this notation, it is possible to give a name to the range and use it in the QUERY. This step certainly makes it easier to identify the group of data that you want to query.
You can do this by selecting the cells of interest then, from the Data menu, select 'Named ranges', give a name, for example 'popolazione_mondiale', and click Finish.
At this point I can use the name I have chosen instead of the range, for example with the QUERY:
=QUERY("popolazione_mondiale", "SELECT A, B, D LIMIT 10", 1)
I am selecting from my named range only column A, B and D for the first 10 rows.
As in common SQL, the WHERE clause is provided to filter the data. For example, if I wanted to isolate all European countries I could write a query like this:
=QUERY("popolazione_mondiale", "SELECT * WHERE C = 'Europe'", 1)
To sort them in alphabetical order just use ORDER BY indicating the column with the names of the countries:
=QUERY("popolazione_mondiale", "SELECT * WHERE C = 'Europe' ORDER BY B", 1)
With DESC I get the descending order:
=QUERY("popolazione_mondiale", "SELECT * WHERE C = 'Europe' ORDER BY B DESC", 1)
With the queries I can also replace the arithmetic operations, I am referring for example to calculating the total of the European population, where I do not need to use the SUM function because with the queries I can use the GROUP BY:
=QUERY("popolazione_mondiale", "SELECT C, sum (D) WHERE C = 'Europe' GROUP BY C", 1)
Same result as the sum obtained in conventional methods.
In the same way I can request the average:
=QUERY("popolazione_mondiale", "SELECT C, avg (D) WHERE C = 'Europe' GROUP BY C", 1)
The clauses can obviously be used together, for example with this QUERY I get the total population per continent and the number of countries per continent in descending order of population number:
=QUERY("popolazione_mondiale", "SELECT C, count (B), sum (D) GROUP BY C ORDER BY sum (D) DESC", 1)
As you have seen, with the knowledge of basic SQL commands you can replace many formulas such as those to obtain operations, filters, conditional sums, vertical searches and many others.
It is also possible to create much more complex QUERIES that involve the use of integrated functions such as filters, obtaining pivot tables and managing dates as well as advanced techniques to add the row with the totals to the formulas and manipulate the data in a real way deep.
If you are interested in learning more about the topic let me know in the comments, I will modulate the next videos based on what interests you and other users subscribed to the channel most.
I hope you enjoyed this video, I'm waiting for your feedback and see you at the next formula for Google Sheets, bye;)
#GoogleSheets #Spreadsheet #QUERY
~ - ~~ - ~~~ - ~~ - ~ -
Please watch: "Create a Web App in 3 Minutes with Google Apps Script"
• Crea una Web app in 3 Minuti con Google Ap...
~ - ~~ - ~~~ - ~~ - ~ -