In Mysql:
I have customer name (primary key), cities and an amount in table:
Table Cities:
customer location amount Cust1 New York, USA 200 Cust2 New York, USA 300 Cust3 Chicago, USA 100 Cust4 Paris, France 400 Cust5 Nice, France 500 Cust6 Milan, Italy 600 Cust7 Mumbai, India 0
The format of location name in this table is:
<city>, <country>
Same as:
<city><comma><space><country>
Table Country (Primary key):
Name USA France Italy India Thailand
I want to get how many cities each country has, and the average amount of each country. Like:
Country Count Average USA 3 200 // (200 + 300 + 100) / 3 France 2 450 // (400 + 500) / 2 Italy 1 600 // (600) / 1 India 1 0 // (0) / 1 Thailand 0 0 // 0
So, my query is:
SELECT t1.name Country, count(distinct t2.location) Count
FROM Country t1 LEFT JOIN Cities t2
ON t2.location LIKE concat('%, ', t1.name)
GROUP BY t1.name ORDER BY Count DESC
But it does not give Average data, it only gives Country name and Count
Advertisement
Answer
Here is one way to do it:
select co.name, count(*) cnt, coalesce(avg(amount), 0) avg
from countries co
left join cities ci
on ci.location like concat('%, ', co.name)
group by co.name
order by co.name
Note that the way you store your data is inefficient. You should:
separate the city name from the country in two different columns
have a primary key in the countries table, and reference it in the cities table
For your dataset, this would be:
Countries
id | name -- | --------- 1 | USA 2 | France 3 | Italy 4 | India 5 | Thailand
Cities
id | customer | location | country_id | amount -- | -------- | -------- | ---------- | ------ 1 | Cust1 | New York | 1 | 200 2 | Cust2 | New York | 1 | 300 3 | Cust3 | Chicago | 1 | 100 4 | Cust4 | Paris | 2 | 400 5 | Cust5 | Nice | 2 | 500 6 | Cust6 | Milan | 3 | 600 7 | Cust7 | Mumbai | 4 | 0