Error en consulta MySQL

[gnzsoloyo]

Si te vas a poner a hacer consultas a la base, te recomiendo enfáticamente estudiar un poco de BBDD y SQL hasta que deje de soanrte a "chino", como dices. No es posible trabajar en algo que no se entiende.
Esa es una observación que se cumple en todo lo referido a informática.

Hay un par de detalles:
1) DISTINCT devuelve registros diferentes, considerando todos los campos que indican en el select, y no sólo el primer campo, por lo que en realidad te devovlería un registro por cada acuario, alias y "tipo" (que no sé que es), como conjunto.
¿Eso lo entiendes?
Puede devolverte el mismo acuario N veces según la cantidad de alias encontrados relacionados al mismo acu_no, y cada uno de esos pares de datos N veces, una por cada "tipo" encontrado.
Con que un registro tenga un valor distinto en el mismo campo entre dos registros, devolverá ambos.
¿Queda claro?

2) AVG() es la media aritmética o promedio. Sólo aplica a campos numéricos ,por lo que el resultado dependerá de lo que se campo tenga.
Por otro lado, la consulta como la pusiste no funcionará con esa funcion en la subconsulta, porque sólo devolvería la que sea igual al promedio de todos... y no será la que mas tenga.

Ahora bien, voy a suponer que el campo "especie" contiene o un ID o bien el nombre de una especie determinada. Digo suponer porque en realidad no lo estás diciendo claramente, lo infiero por la consulta.
También voy a suponer que "alias" es alguna denominación única para cada acuario.
Y voy a suponer además que la consulta la creas programáticamente, desde una aplicación, porque de lo contrario no puedes indicar el "X".

Código MySQL:

SELECT acu_no, alias, tipo, COUNT(DISTINCT F.especie) especies
FROM Acuarios A INNER JOIN Fauna F ON A.acu_no = F.acu_no 
WHERE F.especie 
GROUP BY acu_no, alias, tipo
ORDER BY especies DESC
LIMIT X
 

Suponiendo que X fuese iguala 12, esto puede funcionar si lo ejecutas en una consola de MySQL:

Código MySQL:

SET @x = 12;
SELECT acu_no, alias, tipo, COUNT(DISTINCT F.especie) especies
FROM Acuarios A INNER JOIN Fauna F ON A.acu_no = F.acu_no 
WHERE F.especie 
ORDER BY especies DESC
LIMIT @x;
 

Claro, siemrpe que uses MySQL 5.4 o superior...