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!

Improve this question
1
  • 1
    Please consider reading this advice Commented Jul 8, 2020 at 15:19

1 Answer 1

Reset to default
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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.