Find total count and average based of values from another table

Question

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

Answer 1

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