CODEWITHSUNDEEP
sqlgoogle-bigquery
kee
kee

Reputation: 11629

BigQuery: How to simplify this SQL

This might not be limited to BigQuery but SQL in general.

I have this SQL and want to exclude when the 1st field ("code") is NULL.

SELECT REGEXP_EXTRACT(data, r'#\d+') code, COUNT(1)
FROM mydataset.mytable
GROUP BY 1

This is what I ended up found working:

SELECT REGEXP_EXTRACT(data, r'#\d+') code, COUNT(1)
FROM mydataset.mytable
WHERE REGEXP_EXTRACT(data, r'#\d+') is not NULL
GROUP BY 1

This doesn't work:

SELECT REGEXP_EXTRACT(data, r'#\d+') code, COUNT(1)
FROM mydataset.mytable
WHERE code is not NULL
GROUP BY 1

I can go with what I found working but am wondering if there is more elegant way of doing the same. I guess I can add another outer SELECT but that's not what I want.

Upvotes: 0

Views: 54

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

SELECT REGEXP_EXTRACT(data, r'#\d+') code, COUNT(1)
FROM mydataset.mytable
GROUP BY 1
HAVING code IS NOT NULL

Upvotes: 1

Related Questions