BigQuery Case - for col1 if col 2 equals 'a' & 'b' then True else = False

user_13662366 0
Viewed 448

I'm really struggling with the syntax here...

I'm trying to get the following data:

Col1  Col2
001   a
001   b
002   a
003   b
004   a

To show as follows

Col1  Col2  Col3
001   a     True
001   b     True
002   a     False
003   b     False
004   a     False

I'm in big query and know my statement isn't correct but was looking at something like this:

case
when Col1 is not null then
case 
    when Col2 = 'a' and Col2 = 'b' then "True"
else "False"
end
Duplicates

I'm sure the issue is with the first condition, but can't figure out how to handle it. Could someone please give me some direction?

google-bigquery conditional-statements case sql
2 Answers

Below is for BigQuery Standard SQL

#standardSQL
SELECT *,
  2 = COUNT(DISTINCT IF(Col2 IN ('a', 'b'), Col2, NULL)) OVER(PARTITION BY Col1) AS Col3
FROM `project.dataset.table`

if to apply to sample data from your example - output is

Row Col1    Col2    Col3     
1   001     a       true     
2   001     b       true     
3   002     a       false    
4   003     b       false    
5   004     a       false
user_52219440

Assuming you have only 'a' and 'b', you can use case and window functions:

select t.*
       ( (col2 = 'a' and countif(col2 = 'b') over (partition by col1) > 0 or
         (col2 = 'b' and countif(col2 = 'a') over (partition by col1) > 0
       ) as col3     
from t;
user_11440350
Related