0

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!

2
  • 1
    Please consider reading this advice
    – mustaccio
    Jul 8 '20 at 15:19
  • Thanks Mustaccio, it helped!
    – Matias
    Jul 8 '20 at 15:55
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.