Error en consulta MySQL

[darroyocalatayud]

Buenos días.

Os pongo en antecedentes, estoy creando una aplicación de IOS/Android para la gestión de acuarios, para ambito particular.

Dicha aplicación hace consultas a una base de datos MySQL, en total solo va ha hacer una consulta y menos mal.

La consulta trata de solicitar los "X" acuarios con mas Fauna(peces), "X" es un parámetro que le pasa el usuario.

Bueno no me enrollo y pongo la consulta que estoy intentando hacer

Las tablas:

Acuarios_____________
acu_no
alias
tipo
litros
largo
ancho
alto


Fauna______________
acu_no
especie
fecha_desde
fecha_hasta

Acuarios->Fauna

Código SQL:

SELECT DISTINCT acu_no, alias, tipo FROM Acuarios WHERE Acuarios.acu_no=Fauna.acu_no AND COUNT(fauna.especie) > AVG(COUNT(fauna.especie)) LIMIT "X"
 

Me he quedado atascado en este punto de mi proyecto, ya que la SQL no funciona, soy un negado lo se(Base de datos no es lo mio), me podéis guiar para poder solucionarlo.

[gnzsoloyo]

A tu consulta le falta la tabla Fauna, que usas pero no invocas en el FROM.
Eso para comenzar.
Luego, no puedes usar funciones agregadas en el WHERE, no las admite, y están mal planteadas.

[darroyocalatayud]

Iniciado por gnzsoloyo

A tu consulta le falta la tabla Fauna, que usas pero no invocas en el FROM.
Eso para comenzar.
Luego, no puedes usar funciones agregadas en el WHERE, no las admite, y están mal planteadas.

Lo siento, pero no te sigo todo lo relacionado a BBDD me suena a chino, si no es mucha molestia me puedes guiar algo por donde tirar... estoy atascado en este punto.

EDITO:

Acabo de probar esto y seguimos igual... voy por el buen camino¿?

Código SQL:

SELECT DISTINCT acu_no, alias, tipo 
FROM Acuarios, Fauna
WHERE Acuarios.acu_no = Fauna.acu_no AND Fauna.especie > (SELECT AVG(Fauna.especie) FROM Fauna)
 

[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...

[darroyocalatayud]

Ya lo he solucionado gracias.

Al final me quedo así

Código SQL:

SELECT acu_no, alias COUNT(fauna.especie)
FROM acuarios, fauna WHERE acuarios.acu_no = fauna.acu_no
GROUP BY acuarios.acu_no, alias ORDER BY COUNT(fauna.especie) DESC LIMIT 10
 

gracias por la ayuda

[gnzsoloyo]

Me encantaría saber qué tenían que ver los acuarios mencioandos por ti al principio, con las empresas...

En serio.



Para futuros casos, te recomiendo postear los casos reales, no imamginarios. Ayuda a evitar soluciones inaplicables y perder el tiempo con detalles que no existen.

Por otro lado, a pesar de que lo encuentres en los manuales, no es eficeinte en MySQL 5.0 o superior usar el WHERE para definir relaciones de tablas. Los JOIN implicitos (la coma) tiende a generar productos cartesianos, lo que es un espanto.

Te recomiendo suar JOINs explicitos, así como NO usar funciones de agregación en el ORDER BY, ya que destruyen la performance. En este ultimo caso, eso se hace usando HAVING.
Adicinalmente: NUNCA dejes una columna donde uses una función sin un alias.

Código MySQL:

SELECT E.emp_no, E.firs_name COUNT(T.title) titulos
FROM employees E INNER JOIN titles T ON E.emp_no = T.emp_no
GROUP BY E.emp_no, E.firs_name 
ORDER BY titulos
LIMIT 10
 

Posdata: Si la hubieses probado en realidad, esa consulta que escribiste te hubiese disparado un error de "columna ambigua".
¿Lo notaste?