CODEWITHSUNDEEP
google-bigquery
H. Tao
H. Tao

Reputation: 31

Nth(n,split()) in bigquery

I am running the following query and keep getting the error message:

SELECT NTH(2,split(Web_Address_,'.')) +'.'+NTH(3,split(Web_Address_,'.')) as D , Web_Address_
FROM [Domains.domain 
limit 10

Error message:
Error: (L1:110): (L1:119): SELECT clause has mix of aggregations 'D' and
fields 'Web_Address_' without GROUP BY clause
Job ID: symmetric-aura-572:job_axsxEyfYpXbe2gpmlYzH6bKGdtI

I tried to use group by clause on field D and/or Web_address_, but still getting errors about group by.

Does anyone know why this is the case? I have had success with similar query before.

Upvotes: 3

Views: 2183

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

You can also consider using URL functions: HOST, DOMAIN and TLD

Upvotes: 0

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14004

You probably want to use WITHIN RECORD aggregation here, not GROUP BY

    select concat(p1, '.', p2), Web_Address_ FROM 
(SELECT 
  NTH(2,split(Web_Ad`enter code here`dress_,'.')) WITHIN RECORD p1,
  NTH(3,split(Web_Address_,'.')) WITHIN RECORD p2, Web_Address_ 
FROM (SELECT 'a.b.c' as Web_Address_))

P.S. If you just trying to cut off first part of web address, it will be easier to do with RIGHT and INSTR functions.

Upvotes: 2

Related Questions