Menu
Using SPLIT() & NTH(), I'm splitting a string value, and taking the 2nd substring as the result. I then want to group on that result. However, when I use SPLIT() in conjunction with a GROUP BY, it keeps giving the error:
Error: (L1:55): Cannot group by an aggregate
The result is a string, so why is it not possible to group on it?
For example, this works and returns the correct string:
SELECT NTH(2,SPLIT('FIRST-SECOND','-')) as second_part FROM [FOO.bar] limit 10
But then grouping on the result does not work:
SELECT NTH(2,SPLIT('FIRST-SECOND','-')) as second_part FROM [FOO.bar] GROUP BY second_part limit 10
888
Working with Strings in Google BigQuery It divides value using the delimiter argument. For STRING , the default delimiter is a comma. For BYTES , you must specify a delimiter. Splitting with an empty delimiter generates an array of UTF-8 characters for STRING values and an array of BYTES for BYTES values.
The BigQuery CONCAT function allows you to combine (concatenate) one more values into a single result. Alternatively, you can use the concatenation operator || to achieve the same output.
COALESCE(expr[, ... ]) Description. Returns the value of the first non-null expression. The remaining expressions are not evaluated.
The Concatenation can be performed in BigQuery using the BigQuery CONCAT function or using the concatenation operator (“ || “). Both methods combine two or more BYTE into a single entity. BigQuery CONCAT String can combine STRING entities.
My best guess would be that you can get an equivalent result by using a subquery. Something like :
SELECT * FROM (Select NTH(2,SPLIT('FIRST-SECOND','-')) as second_part FROM [FOO.bar] limit 10) GROUP BY second_part
The system returns Nth in an aggregate internally I guess
96
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
