Problem with a group by SQL query in Access

[Ros@]

I'm trying to make an SQL query in Access like that:

SELECT MAX(COUNT(*)) FROM ALUMNOS
GROUP BY POBLACION;

I want to obtein which is the POBLACION with more alumnos, but I get an
error "You can't put a group by function in the expresion"
The same query has success in Oracle.
Is there any way to solve this in Access?

Thank you.

 

[Douglas J Steele]

If I'm understanding your question correctly, try

SELECT POBLACION, MAX(COUNT(*)) FROM ALUMNOS
GROUP BY POBLACION;

 

[Ros@]

I obtein the same wrogn result.

If I put
select count(*) from alumnos group by poblacion;

or

select max(item) from alumnos group by poblacion;

there is no problem, but if I put a group by function inside another group
by function is when the query fails.
But I insist I have made the same query in Oracle and there have not been
problem, so, the query must be right, musn´t it?

 

[Douglas J. Steele]

Just because it works in Oracle doesn't mean it'll work in Access (or other
DBMS, for that matter). While there is a standard for SQL, virtually all the
manufacturers allow some slight variation from that. Access is probably the
worst when it comes to variations.

But now that I look at it, what I suggested (SELECT POBLACION, MAX(COUNT(*))
FROM ALUMNOS GROUP BY POBLACION) really doesn't make any sense. For each
different value of Poblacion, there will only be a single value for
Count(*), so it's redundant to look the maximum count. Max(item) is
definitely wrong, though: that will simply tell you the largest value of
item, not how many there are.

 

[Ros@]

I´ve find out how to solve this problem, the SQL query is like that:

select max(nameitem) from (select count(*) as nameitem from alumnos group by
poblacion);