i've been trying to do this on php, reading from a db table in mysql, but i cant understand what's the error on it. can someon help me please? the table as a column that contains numbers (this numbers represent a course :P)
SELECT *, MAX(COUNT(mi_curso)) maximo
FROM producto
GROUP BY mi_curso
and this is the error shown:
consulta SQL:
SELECT *, MAX(COUNT(mi_curso)) maximo
FROM producto
GROUP BY mi_curso LIMIT 0, 25
MySQL ha dicho: Documentación
#1111 - Invalid use of group function
could you try the following and comment if this worked?
SELECT id, COUNT(mi_curso) maximo FROM producto GROUP BY mi_curso having max(count(mi_curso))
edit: as suggested by Tim Biegeleisen
SELECT id, COUNT(mi_curso) maximo FROM producto GROUP BY mi_curso having max(count(maximo))
You are trying to nest two aggregate functions, which is not working. Instead, use one of the following two queries:
SELECT *, MAX(mi_curso) maximo
FROM producto
GROUP BY mi_curso
or
SELECT *, COUNT(mi_curso) maximo
FROM producto
GROUP BY mi_curso
How about probably the most common problem with group by....
Including non-aggregates in the select list....
select * probably contains some column other than mi_curso. If so, this group by doesn't work.
You can select aggregates, and columns included in the GROUP BY expression.
If you do (for example)
select name, address, count(name)
from residences
group by name
What value should sql deliver for address when one person has >1 residences? No way for it to know.
Some sql's give nice clear error messages. Some just wink at you, as if you already know what's wrong.