Cannot use WHERE together with other formatting options

Question

I'm using Google Sheet's QUERY function to import data from another tab, add two additional column 'Year' and 'Month' which are generated using column H

=QUERY(earnings!A:J, "SELECT A, B, C, YEAR(C), toDate(C) label YEAR(C) 'Year', toDate(C) 'Month' format toDate(C) 'MMM'", 1)

I want to filter the results to only show certain rows but I get an error when trying to add a WHERE clause to this query.

For example:

=QUERY(earnings!A:J, "SELECT A, B, C, YEAR(C), toDate(C) label YEAR(C) 'Year', toDate(C) 'Month' format toDate(C) 'MMM' where A = 'Brooklyn'", 1)

Returns an error

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "where" "where "" at line 1, column 120. Was expecting one of: "options" ... "," ...

I've tried placing the WHERE clause different places but couldn't get it to work properly. How can I add it to this query?

Here's how my data looks like:

Area	Amount	Date
Brooklyn	$500	01/02/2025
Chicago	$600	01/03/2025
Huston	$700	01/04/2025

Kindly share a minimal, reproducible example—no sensitive data with your desired output—that the community can use to replicate and see what you'd like to do. You may use Tables or tools like Table to Markdown that can easily be copied and pasted. If necessary, as a last resort, Make an anonymous sample document. — Saddles
– Saddles, Commented Mar 24 at 21:10
@Saddles I added the table, and your answer is correct! Thank you. — martin schwartz
– martin schwartz, Commented Mar 24 at 21:22

Saddles · Accepted Answer · 2025-03-24 22:04:37Z

The where A = 'Brooklyn' should be placed before the label for the query statement to work.

As per Language Clauses:

The syntax of the query language is composed of the following clauses. Each clause starts with one or two keywords. All clauses are optional. Clauses are separated by spaces. The order of the clauses must be as follows:

Clause	Usage
select	Selects which columns to return, and in what order. If omitted, all of the table's columns are returned, in their default order.
where	Returns only rows that match a condition. If omitted, all rows are returned.
group by	Aggregates values across rows.
pivot	Transforms distinct values in columns into new columns.
order by	Sorts rows by values in columns.
limit	Limits the number of returned rows.
offset	Skips a given number of first rows.
label	Sets column labels.
format	Formats the values in certain columns using given formatting patterns.
options	Sets additional options.
from	The from clause has been eliminated from the language.

Note: This hierarchy is from top to bottom, starting from select to options.

martin schwartz · Accepted Answer · 2025-03-24 21:23:53Z

1

As @Saddles pointed out in the comments, the WHERE clause should go before the lablel like this =QUERY(earnings!A:J, "SELECT A, B, C, YEAR(C), toDate(C) where A = 'Brooklyn' label YEAR(C) 'Year', toDate(C) 'Month' format toDate(C) 'MMM'", 1)

answered Mar 24 at 21:23

martin schwartz

9818 silver badges17 bronze badges

Collectives™ on Stack Overflow

Cannot use WHERE together with other formatting options

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related