Question

Write a MySQL query which will return all cities with more customers than the average number of customers of all cities. For each such city, return the country name, the city, and the number of customers. Order result by country name ascending

Answer 1

To identify cities with more customers than the average, you write a MySQL query involving a subquery that calculates the city-wide average customer count, and then use this value in a HAVING clause to filter cities with higher customer counts, presenting the result with country name, city, and customer count.

Step-by-step explanation:

To write a MySQL query that returns all cities with more customers than the average number of customers of all cities, along with the country name, city, and number of customers, with results ordered by country name ascending, you need to calculate the average number of customers per city first, then compare each city's customer count to this average.

The SQL query would involve the use of a subquery to calculate the average number of customers across all cities and then use this subquery in a HAVING clause to filter out the cities with customer counts greater than this average. Here's an example of what the query might look like:

SELECT country, city, COUNT(customer_id) as customer_count
FROM customers
GROUP BY country, city
HAVING customer_count > (
SELECT AVG(customer_count) FROM (
SELECT city, COUNT(customer_id) as customer_count
FROM customers
GROUP BY city
) AS city_average
)
ORDER BY country ASC;

Please note that the actual table and column names may vary based on your specific database schema. Replace customers, country, city, and customer_id with the actual table and column names in your database.