Return average using same table data

Question

I've got this query I'm testing with:

select sum(units) / (select count(distinct week) from record_sales_data where customer_country_id=2 group by customer_country_id) as average
from record_sales_data
where customer_country_id=2
group by week,customer_country_id

Which returns what I want for country 2 of course. Now I'd like to return the average for ALL countries, but I don't seem to find a way to return by all countries, grouped by country id..

In the example above, here's what country 2 looks like in the raw data: (there are many more countries of course)

year|month|week|customer_country_id|units|
----|-----|----|-------------------|-----|
2020| 5| 21| 2| 1|
2020| 5| 21| 2| 36|
2020| 5| 22| 2| -1|
2020| 5| 22| 2| -1|

I apologize if this is a simple to resolve question but I'm still learning SQL and can't figure out an easy way to come up with a solution to this.

Thanks!

• mustaccio: Please consider reading this advice

Answer 1

I just solved it by doing this:

select sum(units) / (select count(distinct week) from record_sales_data rs2 where rs.customer_country_id=rs2.customer_country_id group by customer_country_id) as average
from record_sales_data rs
group by customer_country_id