- who are interested in SQL, but who are troublesome to prepare the environment and data from scratch
- who are extracting data with vlookup, but who feel the limit
- who want to analyze data
- Even non-programmers who want to use queries
- who are asking queries delicious? (meaning that the query is delicious)
Query function series planned (planned)
- Outline and purpose
- Syntax of query function
- Basic SELECT clause (get all, get only specific columns)← this time
- Extraction basics Try using WHERE clause (numerical value match, size, date match, size etc.)
- Try using the GROUP BY clause
- Let’s bring data from another sheet ~ Combination with ImportRange function ~
- Sorting basics ORDER BY clause
- Required if you do SQL Try a subquery
- There are line breaks in the data. contains/starts with helps when you can’t handle like
Query function：Basic SELECT Clause
We will publish the original data on the URL for future use. This data is identical to the sample published in the Google Visualization API.（https://developers.google.com/chart/interactive/docs/querylanguage#Select）
Get only one specific column
To get only column A
=QUERY(data ,"SELECT A")
If you specify Column after SELECT, you can get only the row data of that column.
Get multiple columns
in case of acquiring columns A, B, D
=QUERY(data ,"SELECT A, B, D")
If you specify after the SELECT, separated by commas, they are displayed in the specified order.
If you change the order of the columns after SELECT, you can change the order of the displayed data.
=QUERY(data ,"SELECT D, A, B")
By the way, if multiple same columns are specified as below, an error will occur.
=QUERY(data ,"SELECT D, A, B, D") #Error
=QUERY(data ,"SELECT *")
Means all in the select statement, and if you make it as above, you can get all the data
I think that I will not use it much, but I can get all even if I do not set the query.
We looked at SELECT, which is the most basic of queries.
The basis of extraction is to first decide which column data is used in the target data from which data is extracted.
I think that the SELECT statement is not difficult, so please familiarize yourself with it.
Series： Let’s Use Spreadsheet Query Function
While not studying SQL, I will become accustomed to using the spreadsheet query function. We will study from the simple usage of select statement, including the difference from SQL.
- Let’s Use Spreadsheet Query Function (1) Outline and Purpose
- Let’s Use Spreadsheet Query Function (2) Structure of query function
- Let’s Use Spreadsheet Query Function (3) SELECT Clause
- Let’s Use Spreadsheet Query Function (4) WHERE Clause
- Let’s Use Spreadsheet Query Function (5) Group by
- Let’s Use Spreadsheet Query Function (6) Let’s bring data from another sheet ~ Combination with ImportRange function ~
- スプレッドシートQuery関数を使ってみよう ⑦並び替えの基本 ORDER BY句
- スプレッドシートQuery関数を使ってみよう ⑧SQLやるなら必須 Query関数でもサブクエリをやってみる
- スプレッドシートQuery関数を使ってみよう ⑨データに改行が入ってるぞ etc クエリ関数エラー対策