CODEWITHSUNDEEP
sqlgoogle-bigquery
SJK
SJK

Reputation: 29

GBQ SQL: Counting instances of specific fields/rows

I have this table:

Field1   Field2
01       A
01       B
02       C
03       D
04       D

I need to find 2 different types of scenarios.

Scenario 1 Expected Results:

Field1   Field2
01       A
01       B

Scenario 2 Expected Results:

Field1   Field2
03       D
04       D

I think I will need to break this into 2 different SQL queries. Any ideas?

Upvotes: 0

Views: 40

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Consider below (BigQuery Standard SQL)

#standardSQL
SELECT 'Scenario 1' AS scenario, 
  Field1 AS Field, STRING_AGG(DISTINCT Field2) AS Assignments 
FROM `project.dataset.table`
GROUP BY Field1 
HAVING COUNT(DISTINCT Field2) > 1 
  UNION ALL
SELECT 'Scenario 2' AS scenario, 
  Field2 AS Field, STRING_AGG(DISTINCT Field1) AS Assignments 
FROM `project.dataset.table`
GROUP BY Field2 
HAVING COUNT(DISTINCT Field1) > 1

If to apply to sample data from your question - output is

Row scenario    Field   Assignments  
1   Scenario 1  01      A,B  
2   Scenario 2  D       03,04

Note: above solution allows you to run all in one query Assumption here though is that both fields are of STRING data type. If this is not a case - you just need to add CAST(... TO STRING) for respective field

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use exists/not exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.field1 = t.field1 and t2.field2 <> t.field2
             );

Use not exists for the second condition.

Upvotes: 1

Related Questions