CODEWITHSUNDEEP
sqlgoogle-bigquery
KayEss
KayEss

Reputation: 419

Select rows with multiple values in column SQL BigQuery

I have a dataframe which i want to group by specific columns and then select only rows with multiple values (> 1) in colum FieldID_distinct. Goal is to find distinct FieldID with same DateTime, SerialNumber, geometry, FarmName, FieldName.

My query

select DateTime, SerialNumber, geometry, FarmName, FieldName,
ARRAY_AGG(DISTINCT FieldID) AS FieldID_distinct
from `xxx.yyy.zzz`
group by 1,2,3,4,5
order by DateTime ASC

I tried

where FieldID_distinct> 2

and

having FieldID_distinct> 2

but failed.

Upvotes: 0

Views: 1542

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You can just count the values in a having clause:

select DateTime, SerialNumber, geometry, FarmName, FieldName,
       ARRAY_AGG(DISTINCT FieldID) AS FieldID_distinct
from `xxx.yyy.zzz`
group by 1, 2, 3, 4, 5
having count(distinct FieldID) > 2
order by DateTime ASC;

Upvotes: 1

SauriBabu
SauriBabu

Reputation: 434

for Oracle you have to make use of count() function having count(FieldID_duplicate) >2 .

Upvotes: 1

Related Questions