How to return null values if there is no data to display in BigQuery [duplicate]

Question

My question is I want to get null values when there is "no data to display" in the BigQuery.

like this:

But it only works when there are only aggregate functions. How to modify below query so that returns null values?

My query:

 select oid, date, coalesce(sum(quantity_sold),0) as quantity_sold
 from table
 where oid = 'xxx' and (date >= 'xxx' and date <= 'xxx')
 group by 1,2

Answer 1

I found this similar SO question but it creates a column that contains a message that says "Results not found" and assigns null values to other columns. You can apply this query and remove the message and retain only the null values, your query will look like this:

with sample_data as (
select 123 as oid, '2022-01-01' as date, 23 as quantity_sold 
union all select 111 as oid, '2022-01-02' as date, 24 as quantity_sold 
),

actual_query as (
    select oid,date,coalesce(sum(quantity_sold),0) as quantity_sold 
    from sample_data  
    where oid = 534 and (date >= '2021-03-23' and date <= '2021-04-23')
    group by 1,2
)

-- query below is the modified query from the linked SO question above

select actual_query.* 
from actual_query
union all
select actual_query.*  -- all the `actual_query` columns will be `NULL`
from (select 1) left join
     actual_query
     on 1 = 0  -- always false
where not exists (select 1 from actual_query);

Sample output:

NOTE: I created random values for sample data that could mimic the message "There is no data to display" when I ran your query.