MySQL how to get correct count of all desired fields in table

Question

Let's say i have a table:

ID, City1, City2, City3, Country, .... (not important)

The application asks people where would they like to live in let's say France. So it is mandatory to add at least one city, but you can add 3 max cities.

So for instance we have in table data:

ID    City1    City2    City3    Country   UserID
--------------------------------------------------
1     Paris      /        /      France      1
2     Paris    Nice       /      France      2
3     Paris    Nice       /      France      3
4     Nice     Paris    Lyon     France      4
5     Lyon     Paris    Nice     France      5
6     Cannes   Nice     Paris    France      6
7     Paris    Cannes   Lyon     France      7
--------------------------------------------------

So now i display all users on a page when someone clicks France. Then above users i want to display all cities with number like Paris(n) for example.

so if i write:

    select City1 as city, count(1) as num 
    from table_c 
   where Country = "France" group by City1;

i get Paris(4), but i need to get Paris(7), because i want also display City2 and City3, I do not know how to write such an SQL statement.

I tried with many SQL statements, but then i get couple of times Paris(n) displayed, haw can this be done. If it can be?

Answer 1

In case CITY1 column contain all of the cities, then you can simply do this:

SELECT t.city1,sum(t.city1 = t.city2 + t.city1 = t.city3 + t.city1 = t.city4) 
FROM table_c t
WHERE t.Country = "France"
GROUP BY t.city1

If not, use UNION ALL :

SELECT t.city,count(*) FROM (
    SELECT City1 as city FROM table_c WHERE Country = "France"
    UNION ALL
    SELECT City2 FROM table_c WHERE Country = "France"
    UNION ALL
    SELECT City3 FROM table_c WHERE Country = "France"
    UNION ALL
    SELECT City4 FROM table_c WHERE Country = "France") t
GROUP BY t.city

This will give you the correct results