Nth(n,split()) in bigquery

Question

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.

Sorry, there is a typo. forgot a ']'. The query is SELECT NTH(2,split(Web_Address_,'.')) +'.'+NTH(3,split(Web_Address_,'.')) as D , Web_Address_ FROM [Domains.domain] limit 10 — H. Tao

Mosha Pasumansky Mosha Pasumansky · Accepted Answer · 2015-04-24T17:12:34

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.

Nth(n,split()) in bigquery

2 Answers