BigQuery SQL: How to check dual conditions in CASE expression and assign single value?

user_7818560 0
Viewed 629

Input Data:

Store   Sales   
11      2.5 
12      null
13      0.0
14      5.2

Problem Statement: From above mentioned data, have to check if Sales value is null and <> 0, assign Y in new column named Ignore else leave empty.

Expected Output:

Store   Sales   Ignore
11      2.5     
12      null    Y 
13      0.0     Y
14      5.2

Tried SQL Query:

SELECT
    *,
    CASE WHEN t.Sales IS NULL AND t.Sales <> 0 THEN 'Y' ELSE '' END Ignore,
FROM TABLE1 t

While doing so all value of Ignore column is empty. Not sure where I'm going wrong. Need Help. Thanks in Advance!

google-bigquery conditional-statements case sql
3 Answers
SELECT *,
       CASE WHEN t.Sales IS NULL OR t.Sales = 0.0 
            THEN 'Y' 
       END as Ignored
FROM TABLE1 t
user_5753760

Because of the way that NULLs are handled, you can also write this as:

select t.*,
       (case when t.sales <> 0.0 then null else 'Y' end) as ignore
from table1 t;

However, BigQuery supports booleans, so why not just use a boolean?

select t.*,
       (t.sales = 0.0 or t.sales is null) as ignore
from table1 t;
user_11440350

Below is for BigQuery Standard SQL

#standardSQL
select *, 
  if(ifnull(Sales, 0) = 0, 'Y', '') as `Ignore`
from `project.dataset.table`

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

enter image description here

user_52219440
Related