CODEWITHSUNDEEP
sqlgoogle-bigquery
JoeBoggs
JoeBoggs

Reputation: 301

How to count unique occurences of column in Big Query

Given a table such as:

| ID | Value  |
|-------------|
| 1  | "some" |
| 1  | "some" |
| 1  | "value"|
| 2  | "some" |
| 3  | "some" |
| 3  | "value |
| 3  | "value |

How can I count the number of unique occurrences of value for each ID?

So you end up with a table such as:

| ID | Value  | number |
|-------------|--------|
| 1  | "some" |   2    |
|    | "value"|   1    |
| 2  | "some" |   1    |
| 3  | "some" |   1    |
|    | "value |   2    |

I attempted to use OVER(PARTITION BY ID order by Value) to separate the table by IDs and count the separate values. However this counts the number of unique occurences, but then adds them together. So I end up with a table such as:

| ID | Value  | number |
|-------------|--------|
| 1  | "some" |   2    |
| 1  | "some" |   2    |
| 1  | "value"|   3    |
| 2  | "some" |   1    |
| 3  | "some" |   1    |
| 3  | "value |   3    |
| 3  | "value |   3    |

Is there a way to count the unique values like the second example I gave?

Upvotes: 1

Views: 5185

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is for BigQuery Standard SQL 

#standardSQL
SELECT id, value, COUNT(1) number
FROM `project.dataset.table`
GROUP BY id, value

with result 

Row id  value   number   
1   1   some    2    
2   1   value   1    
3   2   some    1    
4   3   value   2    
5   3   some    1

Upvotes: 3

Related Questions