I have a table in BQ that looks like this:

date          rate 
02/02/22       null
02/01/22       null
01/31/22        1
01/30/22        1.5
01/29/22        0.5

I want to create avg_rate column. I tried simple calculations for averages but because I have a group by statement - it assigns nulls to the avg_rate column. I need each date where the rate is null to grab all sums of rate that are not nulls and divide by rows count (for those that has not nulls for rate) and assign this number to each date. Here is my query:

SELECT
  date,
  SUM(rate) / COUNT(*) AS avg_rate
FROM
  `my_table`
GROUP BY
  1

The output I a getting:

date          avg_rate 
02/02/22       null
02/01/22       null
01/31/22        1
01/30/22        1.5
01/29/22        0.5

Desired output is:

date          avg_rate 
02/02/22        1
02/01/22        1
01/31/22        1
01/30/22        1.5
01/29/22        0.5

CodePudding user response：

You can use coalesce to return the avg grouped by date, and if it's null return the total average of the column instead using a subquery:

select date, coalesce(avg(rate), (select avg(rate) from my_table))
from my_table
group by date

CodePudding user response：

Suppose you have this:

SELECT *
FROM (
  select 1 as i union select 2 union select null
) x;

This will output:

With some aggregate functions added:

select avg(i), count(i), sum(i), count(*) 
from (
   select 1 as i union select 2 union select null
) x;

The output is:

• As you can see count(i) counts the not null values
• and count(*) counts all the values