-1

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
Chique_Code
  • 1,422
  • 3
  • 23
  • 49

2 Answers2

1

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
Zakaria
  • 4,715
  • 2
  • 5
  • 31
-1

Suppose you have this:

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

This will output:

i
1
2
NULL

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:

avg(i) count(i) sum(i) count(*)
1.5000 2 3 3
  • As you can see count(i) counts the not null values
  • and count(*) counts all the values
Luuk
  • 12,245
  • 5
  • 22
  • 33
  • But how am I to apply this when there is a date column as well? The issue is that the query has to group it by day, or else I have an error that my data is neither grouped nor aggregated. – Chique_Code Feb 18 '22 at 18:06
  • When no `GROUP BY` is supplied, all records are grouped into one, and it does not really matter what type the column, that is being `GROUP BY`-ed has. My answer is just showing the basics of `AVG()`, `COUNT()` and `SUM()`m because in your question you said: "I want to create avg_rate column" – Luuk Feb 18 '22 at 19:06