Bigquery - Count how many time a value show up in a column-CodePudding

I have a table like this

Col1 | Col2 | Col3
A    | 1    | 23
B    | 3    | 23
B    | 2    | 64
A    | 4    | 75
C    | 5    | 23
A    | 6    | 12
A    | 2    | 33
B    | 3    | 52
A    | 1    | 83
C    | 5    | 24
A    | 6    | 74

and I need a query that will show how many times the value in Col1 appeared:

Col1 | Col2 | Col3 | Col4
A    | 1    | 23   | 6
B    | 3    | 23   | 3
B    | 2    | 64   | 6
A    | 4    | 75   | 6
C    | 5    | 23   | 2
A    | 6    | 12   | 6
A    | 2    | 33   | 6 
B    | 3    | 52   | 3
A    | 1    | 83   | 6
C    | 5    | 24   | 2
A    | 6    | 74   | 6

How can I do it in BigQuery?

CodePudding user response：

Easier with a window function

select *, count(*) over (partition by col1) as col4
from t;

CodePudding user response：

You just need to use window function

select *, count(col1) over (partition by col