How do one "group by" on the column "BY"?

[Anders Lönnberg]

There seem to be a bug in the parser (or docs or me hehe) but it says that eg the following query is faulty:

QUERY(A:BZ, "SELECT BY, COUNT(BY) GROUP BY BY")
whereas eg the following works:

QUERY(A:BZ, "SELECT BZ, COUNT(BZ) GROUP BY BZ")

According to me this is a parser bug.

Do one need to go by ColXX or is there any way to specify column BY in some other notation? I tried 'BY' and`BY`etc but to no avail.

[ปิยปราชญ์ สมุทรกลาง]

ในวันที่ วันพุธที่ 17 พฤศจิกายน ค.ศ. 2021 เวลา 18 นาฬิกา 14 นาที 48 วินาที UTC+7 and...@ecolyx.com เขียนว่า:

[ปิยปราชญ์ สมุทรกลาง]

Yes 

ในวันที่ อา. 2 ม.ค. 2022 17:05 ปิยปราชญ์ สมุทรกลาง <phartsa...@gmail.com> เขียนว่า:

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/c071fd0d-e8f2-4070-b346-bc4c941dc65an%40googlegroups.com.

[pzbrawl]

Not a bug. Best practice is NOT to use reserved words like "by" as column names. Until such names are got rid of, surround sql references to them with backticks, eg select `by`, ...

PB

[Anders Lönnberg]

@pzbrawl: Well, if naming only worked... Google Visualization API Query column naming works in charts but not in Sheets...

And hence the question. Sheets seem to ignore the header names and sets the column names to either ColX or the name of the column in the sheet.

As seen here one can add an optional third parameter "headers" to force the number of header rows but Sheets still ignores those names and go by A, B in the example below.

So in context of Sheets, this is a bug since one cannot query the column BY.

[Anders Lönnberg]

Hm, I got it to work with backticks now. Strange it did not work when I posted it but well it seem to work with this now:

SELECT `BY`,B, MAX(B), SUM(`CA`) GROUP BY `BY`,B

:)