CODEWITHSUNDEEP
sqlgoogle-bigquery
asdasd32
asdasd32

Reputation: 77

How to check if any value in group is equal to a specific value in BigQuery SQL?

I have a dataset like the following:

ID|Date_Val|Data
1|2022-01-01|A
1|2022-01-01|I
1|2022-01-01|H
2|2022-01-01|G
2|2022-01-01|G
2|2022-01-01|I

I would like to run a query like the following:

SELECT ID, Date_Val, IF(/logic here/, 'A', 'B')
GROUP BY 1,2

Output dataset

ID|Date_Val|Data
1|2022-01-01|A
2|2022-01-01|B

How would I write /logic here/ so that if any Data value in the grouping (ID, Date_Val) is = 'A' then 'A' else 'B'.

Upvotes: 0

Views: 770

Answers (4)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is most compact version so far for you to consider

select id, date_val, 
  if(logical_or(data = 'A'), 'A', 'B') as data
from your_table
group by id, date_val

if applied to sample data in your question - output is

enter image description here

Upvotes: 1

Jaytiger
Jaytiger

Reputation: 12244

In BigQuery, another option would be:

SELECT ID, Date_Val, IF('A' IN UNNEST(ARRAY_AGG(Data)), 'A', 'B') Data
  FROM sample_table 
 GROUP BY 1, 2;

enter image description here

Upvotes: 1

Horaciux
Horaciux

Reputation: 6477

SELECT ID, Date_Val, 
  CASE WHEN SUM(CASE WHEN Data = 'A' THEN 1 ELSE 0 END) >= 1 THEN 'A' ELSE 'B' END as Data
FROM your_table
GROUP BY ID, Date_Val

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

We can try:

SELECT ID, Date_Val,
       CASE WHEN MAX(CASE WHEN Data = 'A' THEN 1 END) > 0 THEN 'A' ELSE 'B' END AS Data
FROM yourTable
GROUP BY 1, 2;

Upvotes: 1

Related Questions