CODEWITHSUNDEEP
selectcountgoogle-bigquery
Levi H.
Levi H.

Reputation: 105

How can I count the number of occurrences of another field in a BigQuery Select statement?

I have the following example data:

id domain
1 test
2 example
3 test
4 main
5 main
6 test

What I want to do in a Select statement is to end with the following:

id domain domain_occurrences
1 test 3
2 example 1
3 test 3
4 main 2
5 main 2
6 test 3

How can I do this?

Upvotes: 0

Views: 457

Answers (1)

Sergey Geron
Sergey Geron

Reputation: 10222

Use count(*) over (partition by domain):

select 
  *,
  count(*) over (partition by domain) as domain_occurencies
from mytabel

Upvotes: 3

Related Questions