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

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'.

CodePudding user response：

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;

CodePudding user response：

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

CodePudding user response：

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;