Tomar el valor más repetido de un GROUP (MySQL)

[Carlillus]

Hola, tengo una duda algo difícil de explicar. Resulta que en la tabla de usuarios, cada usuario tiene como datos el país, la comunidad y la ciudad donde vive.

En mi página hay un apartado de estadísticas, donde, por orden, se muestran todas las ciudades según los usuarios que viven en ellas. Para mostrarlas, lo hago con la siguiente consulta:

Código PHP:

  SELECT ciudad, COUNT(ciudad) AS number, comunidad, pais FROM users GROUP BY ciudad ORDER BY number DESC 
   

Cómo veis, quedan todos los usuarios agrupados por ciudades y tambien se muestra el número de usuarios que hay en cada ciudad. Ahora bien, para mostrar en qué comunidad y en qué país está la ciudad, se mostrará el valor del primer acierto de cada grupo.

Por ejemplo, si el primer usuario de Barcelona va mal de geografía y ha escrito que es de la Comunidad de Madrid, se mostrará esta ciudad cómo si fuese de madrid, independientemente que los demás usuarios hayan introducido la comunidad correcta.

Para evitar esto, había pensado mostrar el valor de la comunidad que más se repita, pero no sé si eso se podría hacer con una consulta o si hay algún modo de sacar un array con todas las comunidades introducidas por ciudad y luego con PHP buscar el que más se repita.

Alguna solución?

Gracias

[huesos52]

Carlillus
Cuando se habla de normalizacion de bases de datos, mucha gente pasa por alto el tema. Pero este caso es un claro ejemplo del mal diseño de tu base de datos. Cuando quieres sacar este tipo de reportes la cosa se puede tornar muy compleja y de trabajo innecesario de programación para tener en cuenta todos los posibles errores de usuario. Imaginate que ingrese en vez de barcelona, barselona(Un usuario con muy mala ortografia ) van a ser usuarios diferentes.

Te doy dos consejos.
1. Normaliza tu base de datos y migra correctamente los datos existentes. Te tomará un poco de tiempo, pero será bien invertido para un futuro. Con normalizacion me refiero a que te crees una tabla pais, otra comunidad y otra cuidad y que las codifiques y las enlaces correctamente a tu tabla de usuarios.

2. Si definitivamente no tienes tiempo y es un reporte urgente, te recomiendo preguntes en el foro de php como podrías solucionar el problema. Desde el motor de base de datos, la consulta sería monstruosa y muy pero muy ineficiente.

saludos