Query function in Google Sheet

0
votes

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" ... ... <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" ... ... <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" ...

1
google-sheets

1 Answers

1
votes

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")