Problema para hacer una consulta

[mortiprogramador]

Saludo.

Pues teniendo en cuenta lo que se plantea, tal vez la mejor manera
sería modificando un poco la base de datos, pero con lo que se tiene
puede sacarse haciendo lo siguiente:

1. Una consulta que haga el conteo de cada platillo por especialidad, y que solo nos retorne el conteo más alto (esto para luego sacar los platillos que tengan esa cantidad de conteo, pues pueden ser varios en caso de empates)
2. Usar la consulta del punto 1 como condición de una consulta más grande, en donde traiga todos los platillos de una determinada especialidad.
3. Usar la consulta del punto 2 para generar UNION SELECT cambiando en cada cual la especialidad a buscar.

Es decir, algo así:

Código SQL:

-- Consulta del punto 2
SELECT e.nombre_especialidad, p.id_platillo, p.nombre_platillo, COUNT(c.id_platillo) vecesconsumidas
FROM borrar_consumo c
INNER JOIN alumno a ON c.id_alumno = a.id_alumno
INNER JOIN especialidad e ON a.id_especialidad = e.id_especialidad
INNER JOIN platillo p ON c.id_platillo = p.id_platillo
WHERE e.id_especialidad = 1
GROUP BY e.nombre_especialidad, c.id_platillo
HAVING COUNT(c.id_platillo) = 
-- Consulta del punto 1
(SELECT COUNT(c1.id_platillo)
FROM consumo c1
INNER JOIN alumno a1 ON c1.id_alumno = a1.id_alumno
INNER JOIN especialidad e1 ON a1.id_especialidad = e1.id_especialidad
WHERE e1.id_especialidad = 1
GROUP BY c1.id_platillo
ORDER BY COUNT(c1.id_platillo) DESC LIMIT 1)
-- Consulta del punto 3, que es la misma del punto 2, con otra id_especialidad
UNION
SELECT e.nombre_especialidad, p.id_platillo, p.nombre_platillo, COUNT(c.id_platillo) vecesconsumidas
FROM consumo c
INNER JOIN alumno a ON c.id_alumno = a.id_alumno
INNER JOIN especialidad e ON a.id_especialidad = e.id_especialidad
INNER JOIN platillo p ON c.id_platillo = p.id_platillo
WHERE e.id_especialidad = 2
GROUP BY e.nombre_especialidad, c.id_platillo
HAVING COUNT(c.id_platillo) = 
(SELECT COUNT(c1.id_platillo)
FROM consumo c1
INNER JOIN alumno a1 ON c1.id_alumno = a1.id_alumno
INNER JOIN especialidad e1 ON a1.id_especialidad = e1.id_especialidad
WHERE e1.id_especialidad = 2
GROUP BY c1.id_platillo
ORDER BY COUNT(c1.id_platillo) DESC LIMIT 1)
................
 

Cómo resultado, se obtendrá lo siguiente:

Código HTML:

nombre_especialidad     id_platillo     nombre_platillo     vecesconsumidas
Aplicaciones                         9                     sopa                 6
Bases de datos                     9                     sopa                 2
Desarrollo                             9                     sopa                 2
Moviles                                 9                     sopa                 2    
Moviles                                 1                     pasta                 2
Sistemas Operativos                 1                     pasta                 3
Desarrollo                             10                 fruta                     2
Desarrollo                             6                     ensalada roja     2
Bases de datos                     7                     ensalada fria         2
Bases de datos                     2                     arroz                 2
 

Y como se puede ver, pues hay empate en especialidades
y algunos platillos (Por ej en Moviles la sopa y la pasta)

Espero sirva la idea.