0

I am using the below query function but getting an error, can anyone help

=QUERY($Q$1:$AD$1, "SELECT A,COUNT(A) WHERE A IS NOT NULL GROUP BY A PIVOT BY C") 
Error - Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "by" "BY "" at line 4, column 7. Was expecting one of: "true" ... "false" ... "date" ... "timeofday" ... "datetime" ... "timestamp" ... "min" ... "max" ... "avg" ... "count" ... "sum" ... "no_values" ... "no_format" ... "is" ... "null" ... "year" ... "month" ... "day" ... "hour" ... "minute" ... "second" ... "millisecond" ... "with" ... "contains" ... "starts" ... "ends" ... "matches" ... "like" ... "now" ... "dateDiff" ... "quarter" ... "lower" ... "upper" ... "dayOfWeek" ... "toDate" ... <ID> ... <INTEGER_LITERAL> ... <DECIMAL_LITERAL> ... <STRING_LITERAL> ... <QUOTED_ID> ... "(" ... "-" ... "min" ... "max" ... "count" ... "avg" ... "sum" ... "year" ... "month" ... "day" ... "hour" ... "minute" ... "second" ... "millisecond" ... "now" ... "dateDiff" ... "lower" ... "upper" ... "quarter" ... "dayOfWeek" ... "toDate" ... "(" ... <STRING_LITERAL> ... <DECIMAL_LITERAL> ... <INTEGER_LITERAL> ... "-" ... "true" ... "false" ... "date" ... "timeofday" ... "datetime" ... "timestamp" ... <ID> ... <QUOTED_ID> ... "min" ... "max" ... "avg" ... "count" ... "sum" ... "no_values" ... "no_format" ... "is" ... "null" ... "year" ... "month" ... "day" ... "hour" ... "minute" ... "second" ... "millisecond" ... "with" ... "contains" ... "starts" ... "ends" ... "matches" ... "like" ... "now" ... "dateDiff" ... "quarter" ... "lower" ... "upper" ... "dayOfWeek" ... "toDate" ...
Improve this question
0

2 Answers 2

Reset to default
1

Take out the "BY" after PIVOT. Also, columns A and C aren't in your range so you probably mean Q and S:

=QUERY($Q$1:$AD$1, "SELECT Q,COUNT(Q) WHERE Q IS NOT NULL GROUP BY Q PIVOT S")

You also have the option of making the range into an array by putting curly brackets round it, then using Col1 and Col3:

=QUERY({$Q$1:$AD$1}, "SELECT Col1,COUNT(Col1) WHERE Col1 IS NOT NULL GROUP BY Col1 PIVOT Col3")
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

I had the same problem in a range and I solved it by putting backticks (`).

The error is because the "BY" column is being interpreted as a keyword in the "QUERY".

By enclosing column names in backticks (`), you tell Google Sheets to treat those names as literal identifiers, which prevents the "BY" column from being confused with the 'BY' keyword.

My query was:

=QUERY('Sheet 1'!A5:CA100,"SELECT BW,BX,BY,BZ,CA WHERE (A <> 0) &"'",0)

And it was fixed like this:

=QUERY('Sheet 1'!A5:CA100,"SELECT `BW`,`BX`,`BY`,`BZ`,`CA` WHERE (A <> 0) &"'",0)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.