Fundamentals of SQL > SQL Subqueries > Multi-row and Multi-column Subqueries in SQL > Screen 6 > Find the average number of sales per country per customer
Question: Find average number of sales per country per customer.
SELECT x.billing_country, AVG(no_sales_country) AS sale_tally FROM customer c JOIN (SELECT customer_id, billing_country, COUNT(*) AS no_sales_country FROM invoice GROUP BY billing_country) AS x ON c.customer_id = x.customer_id GROUP BY c.customer_id ORDER BY no_sales_country DESC
What I expected to happen:
The expected output is:
Czech Republic 15
What actually happened:
billing_country sale_tally USA 131 Canada 76 Brazil 61 France 50 Germany 41 CzechRepublic 30 Portugal 29 UnitedKingdom 28 India 21 Chile 13
Please help to explain why the code produces an incorrect output. First in the nested subquery on JOIN statement, number of sales per country is calculated. Then average of that is calculated in the outer subquery based on per customer. GROUP BY clause is used for grouping the rows first by country (nested subquery) and then by customers.
Thanks in advance.