In this tutorial, we'll teach you how to use Query statements in Awesome Table to: display only the columns of interest, reorder your columns, show items that meet the criteria you've set, sort your items, limit the records displayed, and label your column headers without touching the data source.
1 - What is a Query?
A Query gives you the ability to show only a specific subset of your data by using the Google Visualization Query Language. With Query in Awesome Table, you can:
- View data coming only from selected columns.
- Reorder the columns.
- Rename or relabel column headers.
- Show rows (records) based on specified criteria.
- Sort data.
Applying a Query using the Query expression syntax.
Let's start with the basics - Language clauses. The syntax of the Query language is composed of clauses. Each clause starts with one or two keywords. All clauses are optional. Clauses are separated by spaces. And the order of the clauses must be as follows:
| Order | Clause* | Usage |
|---|---|---|
| 1 | select | Selects which columns to return, and in what order. If omitted, all of the table's columns are returned, in their default order (from top to bottom). |
| 2 | where | Returns only rows that match a condition. If omitted, all rows are returned. |
| 3 | order by | Sorts rows by values in columns. |
| 4 | limit | Limits the number of returned rows. |
| 5 | label | Rename column headers. |
* This is a non-exhaustive list of clauses that are applicable in Awesome Table.
Example using all 5 clauses - following the prescribed sequence or order:
select * where C >= 90 order by C desc limit 10 label C 'Top 10'
Translation:
- select * - Use all columns in the data source.
- where C >= 90 - Only display entries in column C whose value is greater than or equal to 90.
- order by C desc - Sort the numeric entries on column C in descending order (highest to lowest).
- limit 10 - Then display only the first 10 items.
- label C 'Top 10' - Finally, label column C as Top 10.
2 - How to use Queries.
To enter your Query statement in Awesome Table's sidebar menu:
go to ADVANCED PARAMETERS > Query.
3 - Hands-on examples using the Query expression syntax:
We're going to use 3 different AwesomeTable views in explaining how Query clauses work.
| Clause | Demo Template | Description |
|---|---|---|
| select and order by | Board Games | A collection of 6 board games (+ 2 intentionally inserted blank rows). |
| label | Corporate Promotion | A roster of 34 candidates of which only the top 5 will make it through the management training program. |
| limit | Corporate Promotion | A roster of 34 candidates of which only the top 5 will make it through the management training program. |
| where with criteria | Board Games | A collection of 6 board games (+ 2 intentionally inserted blank rows). |
| where with dates | Files Cabinet | A folder containing project files in various formats (Google Docs, Sheets, Slides & graphics). |
• Select and Order by
Copy the Board Games template.
Board Games datasource
|
Task # 1: Display everything contained in the datasource.
select *
Output: 8 cards (including 2 blank ones)
|
|
|
Task # 2: Display the search field first, followed by the remaining columns.
select N,A,B,C,D,E,F,G,H,I,J,K,L,M
Output: 8 cards (including 2 blank ones)
info This is useful if you need to quickly reorder your columns without requiring you to manipulate the datasource. There are times that you need to reorder your view's columns, but is impractical or cumbersome to reorder the underlying datasource columns (e.g. data generated by an external system like Google Forms, database software, ...)
|
|
|
Task # 3: Display everything then sort by Complexity in reverse alphabetical order.
select * order by J desc
Output: 8 cards (including 2 blank ones)
warning You can also specify asc for ascending order.
info To save time, you can omit asc altogether since the Query output is arranged in ascending order by default (low-high or a-z).
|
|
|
Task # 4: Display everything then sort by Complexity in reverse alphabetical order, then by Complexity Level.
select * order by J desc, I
Output: 8 cards (including 2 blank ones)
|
|
• Limit
Copy the Corporate Promotion template
Use-case / scenarios:
Display only the top-ranked items with applications in:
- Education (Top 10 students, Top 100 applicants, Scholarship recipients, ...)
- Corporate (Ranking for: Departmental contest, Grants / funding, promotion…)
Corporate Promotion datasource
|
Task # 5: Show only the Top 5 performers (starting with the highest scorer).
select * order by H desc limit 5
Explanation: Select all columns and only show the Top 5 candidates whose Final Score (column H) is sorted from highest to lowest (descending order). Or you can opt to reference the actual ranking (column I) and sort it in ascending order using this alternative statement: select * order by I limit 5.Output: 5 candidates
|
|
• Label
Copy the Corporate Promotion template
Use-case / scenarios:
Your Google Sheets datasource is linked to Google Forms. You need to rename some headers because some of the the linked-questions from Google Forms are too long. But renaming the datasource headers manually is not recommended. Doing so will create problems with:
- the synchronization of submitted form responses or
- have issues with other Google Sheets add-ons.
Corporate Promotion datasource
|
Task # 6: Relabel / rename the header of columns B and C as Nom de famille and Prénom, respectively.
select * label B 'Nom de famille', C 'Prénom' Explanation: Select all columns and relabel column B's header as "Nom de famille" and column C's header as "Prénom". Output: 34 candidates
info This is useful if you need to quickly relabel your column headers without requiring you to manipulate the datasource. There are times that you need to rename your column headers, but is impractical or cumbersome to modify the underlying datasource columns (e.g. data generated by an external system like Google Forms, database software, ...)
|
|
• Where with Criteria
Copy the Board Games template.
Board Games datasource
|
Task # 7: Display games with Complexity Levels greater than or equal to 3.
select * where I >= 3
Output: 3 cards
|
|
|
Task # 8: Display games with Complexity Levels less than 4.
select * where I < 4
Output: 4 cards (+ 2 blank ones)
|
|
|
Task # 9: Display games aimed at our seasoned players (Tough category).
select * where J = 'Tough'
The simple comparison operator equal to (=) is ideal for use with categoryFilters where only 1 category (per column) can be selected at one time. Output: 2 cards
|
|
|
Task # 10: Display games intended for both the easy and intermediate players.
select * where J = 'Easy' or J = 'Intermediate'
Output: 4 cards
|
|
|
Task # 11: Display games belonging to the category "Card Game".
select * where F contains 'Card Game'
Output: 3 cards
warning String matching is case & space sensitive. The text string you use should be verbatim (or the same) as the one entered in your referenced column.
String Matching examples:
|
|
|
Task # 12: Display game(s) tagged under 2 categories: "Card Game" and "Ancient".
select * where F contains 'Card Game' and F contains 'Ancient'
The comparison operator contains is ideal for use in csvFilters where each item can have multiple categories. Output: 1 card
|
|
|
Task # 13: Your eyes may be getting tired from seeing those red-blank cards. The challenge in this exercise is to prevent blank cards from ever displaying.
(For columns with text strings)
select * where A != ''
Output: 6 cards Any of the alternative statements below will yield the same result.
(For columns with numeric values)
select * where A != 0 Output: 6 cards Any of the alternative statements below will yield the same result.
|
|
• Where with Dates
Copy the Files Cabinet template
Use-case / scenarios:
Focus on project deadlines (open / pending) items with applications in:
- Education
- Training
- Publishing
- Software development
- Project management, etc.
Files Cabinet datasource 
today() function. The number of Query output generated will vary because of this (that is, the Query output will either add or subtract 1 file depending on the current month).
|
Task # 14: Only display project files with deadlines for the current month and year.
select * where month(D) = month(now()) AND year(D) = year(now())
Output: 2 files
|
|
|
Task # 15: Only display delayed project files from the previous year (2017) then sort by deadline in reverse order.
select * where year(D) = 2017 and J = 'Delayed' order by D desc
Output: 6 files
|
|
|
Task # 16: Only display the 2018 project files then sort by status, then by deadline.
select * where year(D) = 2018 order by J, D
Output: 8 files
|
|
|
Task # 17: Only display project files with deadlines the same as (or later than) the current date. Then sort by status in reverse alphabetical order, then by deadline.
select * where D >= todate(now()) order by J desc, D
Output: 5 files
|
|
|
Task # 18: Only display project files that have only been published within the last 6 months - including projects with deadlines the same as (or later than) the current date. Then order by status, then by deadline in reverse order.
select * where datediff(todate(now()), D) <= (30 * 6)
Output: 12 files
|
|
|
Task # 19: Only display project files that have only been published within the last 6 months - excluding projects with deadlines the same as (or later than) the current date. Then order by status, then by deadline in reverse order.
select * where datediff(todate(now()), D) <= (30 * 6) and D < todate(now()) order by J, D desc
Output: 7 files
|
|
Comments
30 comments
Hi,
I have a little problem using LABEL and ORDER clauses at the same time.
If I write:
SELECT A, C, D ORDER BY D --> the query works fine
but with:
SELECT A, C, D LABEL A ' Name', C 'Phone' ORDER BY D --> the query sends an error message!!
Is there a little bug or I'm missing anything?
Thanks in advance for your help!
Gavp
BTW... great web site, product and ideas, congratulations!!
Hello Gustavoavalerop,
Actually, when you use SELECT and ORDER, ORDER must be just after SELECT. I suggest you use:
It should work with this method.
Kind regards.
Thomas
Is it possible to pass query via URL parameter? We would like to limit returned results to a UID that would be passed in the URL view?UID=123456789
Query: select * where UID = {{GET_UID}}
Hello,
Unfortunately this is not possible with Awesome Table.
Nicolas
Great Article! it helped me on some of the things I have been working on.
I have one that I'm asking it to essentially filter more. But once I get to a certain point, Then the rest stops working. What am I doing wrong? In the below formula, A is bringing me Active and inactive. Manager sometimes shows up and sometimes doesn't depending on if I add the Active one or not. Is there a specific way this order should be to make it work?
=QUERY(Lookup!A:Y, "select B, E, F, O, P, Q, W, X where B = 'Operations' and W = 'Supervisor' or W= 'Lead' or W = 'Manager' and A = 'Active' ")
Sean
Hello Sean Gillard,
Actually, your syntax is not correct. You don't need to use"=QUERY(Lookup!A:Y...".
Your query must be like this:
In this way, it will work because that's the correct syntax.
Thank you.
Thomas
I am super new at this. Is there a way to make it so as you filter results in the drop-down filters that the other filters would be dynamic and update with only content relative to the filter before it? If so where would you beginning to set that up?
Thank you so much for your help! Jen
Hello Jennifer,
You could achieve this with CSV and Category filters using dependent filters.
Please let me know if that helps!
Nicolas
Hello
Is it possible to SELECT rows that are not duplicates (based on value in a specified column)?
I can deduplicate in Google Sheets, but was wondering if I could do it in an Awesome Table view.
Best regards
Dave
Hello Dave HUDDART,
It is definitely possible to select rows depending on specific term or criteria. You should consult this part of this article to know how to achieve that.
Kind regards.
Thomas
🙌🙌
The SORT option was VERY helpful; I needed newest data at the top, and doing this through Google Sheets is tedious. This saved me SO much headache!!! ❤❤
Hello Brandon Carraway,
Thank you for your message!
We are really glad you like our product and you find it is useful.
Kind regards.
Thomas
Hi,
Is it possible to add a button to sort the results in diferent ways ? I'd like to have the possibility to sort by one colums (alphebeticaly) or a diferent way by clicking on a button.
Thanks !
Hello Luis Esteves,
There are no ways to change the sorting or query with a button.
Nicolas
Hi,
I need help on this one
=QUERY('Raw Data New'!A:BS,"select A,BN,BO,BQ,BR,BP,BM,H,AA,AB,AH,AI,AJ,AK,AL,AM,AN,X,Y,AQ,AR,AS,AT,AU,AV,AW where BP = date '"&TEXT($AN$7,"yyy-mm-dd")&"'")
AN7 is the cell reference date and I need pull all dates that includes cell reference plus 7 days or 1 week prior to the cell reference date.
Thank you!
Hello Kris S,
We don't deliver specific support for queries, so, I suggest you consult this article that will give you the language to achieve what you want.
Kind regards.
Thomas
How to sort two columns (L, I)
I need to sort by column L desc, then by I desc I tried this: "select * order by L desc and order by I desc" even using a "," instead of "and" as I was reading through the above guide, but I can't seem to figure it out. 🤦♂️
Hi @Brandon.
Instead of select * order by L desc and order by I desc, use this statement:
Please reference Task # 4 above for more info.
Hope this helps.
- Randy
Hello,
is it possible to use a group by statement?
Thank you
Hi @Florentin.
The group by clause can and will work when entered in the Awesome Table editor's Query field.
But I don't recommend using this.
Please note that since the group by clause can only be used with an aggregation function, it will perform the specified aggregation (avg, count, max, min, sum) and in effect:
An alternative would be to use the order by clause to cluster items based on a specified category. More about this on Tasks # 3 - 4 above.
Hope this helps.
- Randy
Hi there,
i have problem
In Table have 4 columns
A B C D
ID 01-jan-2019 02jan-2019 03-jan-2019
1 Yes No Yes
2 Yes Yes Yes
3 No Yes No
4 Yes No Yes
5 No No No
How to get result which shows the same value(s) (yes) on each date? (the same order)
If i search for 2 results per column then the result will be: columns B (rows 1,2), C(rows 2,3 ), D( rows 1,2)
If i search for 3 results per columnd then result will be: column B (rows 1,2,4) and column D (rows 1,2,4)
Hello @zoran perovic,
You could use a filter for several columns and limit the number of items displayed. I suggest you use a category filter for your B, C, and D column. It is definitely possible to achieve that with Awesome Table.
That's why you should consult this article that explains how to set up a stringFilter for several columns but it is the same principle except that you need to use categoryFilter instead of stringFilter. Here are all our available filters.
Kind regards.
Thomas
well thank you Thomas
but i am using excel for work on this
is there any tip how to get this result in excel?
Hi @Zoran.
There seems to be some confusion.
MS Excel is beyond the scope of Awesome Table support (we don't use Excel in our web app).
Please reach out to the proper support channel for your concerns.
Thanks.
- Randy
Hello, is there a template about movies, my question is how can I make a movie have several genres?
Example:
Avengers: Action / adventure / science fiction / fantasy / superheroes
When searching for any of the genres, it appears.
Thank you
Hi @fabricio.
Please scan our Template Gallery to see a sample of a movie template.
And csvFilter is the best option for your needs.
Note: For topics unrelated to any article, please create a new post tagged under General Discussion so we can address all questions accordingly.
- Randy
Hi,
it's possible to show columns based on filters?
I'd like to have it working like this:
If in the filter for column A I select the first value I see only columns B, C and D
If in the filter for column A I select the second value I see only columns E, F and G
Thanks,
Marco
Hi @Marco.
We can only use Queries to show records (rows), but not columns, based on conditions.
- Randy
I have built an awesome table that has topic (COL A), grade (COL B), and curriculum standards met in different subject areas (COL C-K).
I filtered as follow:
COL A CategoryFilter(B) - Hidden
COL B CategoryFilter
The rest of the columns have no filter
I want the results to only show columns with data in them.
Right now a search might yield this:
but I want all the columns with blank entries (in this case COL C,D, F,G, H, etc) removed. Is there a query for this?
Jen
Hi @Jen.
To avoid displaying blank cells (and their corresponding rows) on your view, simply follow the instructions detailed in Task # 13 above. Since you need to specify multiple non-blank columns, you need to use the logical AND operator. For example, the Query statement below will specify that it will only show or display rows where (all 3) columns A, B and F doesn't have blank entries.
> Just replace and/or add the relevant columns in this statement for your use case.
* Don't forget to click the UPDATE VIEW button for the Query statement to take effect.
- Randy
Please sign in to leave a comment.