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

30 views
Skip to first unread message

Anders Lönnberg

unread,
Nov 17, 2021, 6:14:48 AM11/17/21
to Google Visualization API
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.

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

unread,
Jan 2, 2022, 5:05:46 AMJan 2
to Google Visualization API


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

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

unread,
Jan 2, 2022, 5:21:50 AMJan 2
to google-visua...@googlegroups.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

unread,
Jan 2, 2022, 8:32:25 PMJan 2
to Google Visualization API
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

unread,
Jan 5, 2022, 7:06:14 AMJan 5
to Google Visualization API
@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.


Untitled.png

Anders Lönnberg

unread,
Jan 5, 2022, 7:39:41 AMJan 5
to Google Visualization API
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

:) 

Reply all
Reply to author
Forward
0 new messages