This blog post explains to you, how to deal with the quarry function in google sheet.Actually quarries play important role in a every database.So when it becomes to google sheet also same.Lets start our work.
image 01
Basically,we use above data table to learn google query function
We need to assign a name for our data table under name range,before doing this we have to highlight whole table first.
Then you have to assign name for your data table under the Named ranges.So this makes our work easy.To do this you have to click on the data then select Named ranges from the drop down menu.
image 03
image 04
According to this we have Named our data table as "INVOICE" .For Named ranges you have to give this name.
In Google sheets we use the Google Sheets QUERY function and write our SQL code inside this function.So,we'll enter all of our SQL code inside a QUERY function.
Select all data using SQL query function
image 05
By using following query we can select all data from the source table.
=QUERY(INVOICE,"SELECT *",1)
We used above query to generate new data table.According to this query it selects all the details from our original table.when it comes to normal SQL there is a difference.
Under the general SQL function we use "From INVOICES".Therefor the difference you should notice is we do not use FROM clause with the Google Sheets query function.
From above we learned how to get all table details at once. Now it is time to learn the way of selecting specific column tables.
Selecting specific columns using query function.
If we want to select specific details from the example data table above formula can be used.Assume you need to take Invoice number and the Invoice amount from the above table you just needs type following query function to get this;
From above we learned how to get all table details at once. Now it is time to learn the way of selecting specific column tables.
Selecting specific columns using query function.
If we want to select specific details from the example data table above formula can be used.Assume you need to take Invoice number and the Invoice amount from the above table you just needs type following query function to get this;
image 6
=QUERY(INVOICE,"SELECT B, D",1)
To select Invoice date,Invoice amount columns we used above query.
if we explain above query further "INVOICE" is the name given to the main table.It means we have already given this name to Named ranges.Then "B" and "D" are the columns from main table.
Next,try to select Invoice No and Invoice Amount from the main table.To do this query should be designed as follows;
=QUERY(INVOICE,"SELECT A, D",1)
To select Invoice date,Invoice amount columns we used above query.
if we explain above query further "INVOICE" is the name given to the main table.It means we have already given this name to Named ranges.Then "B" and "D" are the columns from main table.
Next,try to select Invoice No and Invoice Amount from the main table.To do this query should be designed as follows;
=QUERY(INVOICE,"SELECT A, D",1)
image 07
Look at the image 07,it shows the result of the query that we have designed recently.
No comments:
Post a Comment