mpotoc mpotoc - 6 months ago 12
MySQL Question

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

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

can you try this sql and let me know if this works

select city, count(1) as num from (
select City1 as city
from table_c
where Country = "France" and city1 is not null
UNION ALL
select City2 as city
from table_c
where Country = "France" and city2 is not null
UNION ALL
select City3 as city
from table_c
where Country = "France" and city3 is not null
) tbl
group by city
Comments