Simple SQL Question

[jgbishop]

I have the following SQL statement:

select A.xyz, max(count(A.xyz))
from ATable A
group by A.xyz;

When I try to run this command, I get an error stating that A.xyz is "not a single-group group function." But I'm grouping by that very attribute at the end of the statement! When I rewrite the statement like this:

select A.xyz, count(A.xyz)
from ATable A
group by A.xyz;

the query works just fine. So, clearly, SQL does not like the fact that I am nesting the count() call inside the max() call. But I need the maximum of the count, not just a listing of everything. Does anyone know how to fix this problem? I essentially want to print out the entity that occurs most in the table, along with an attribute of said entity. I'm stuck!

 

[kamper]

If I understand the problem correctly try this:

select top 1 A.xyz, count(A.xyz)
from ATable A
order by A.xyz
group by A.xyz

If that doesn't work you can always split it into nested queries.

 

[MrChad]

Originally posted by: kamper
If I understand the problem correctly try this:

select top 1 A.xyz, count(A.xyz)
from ATable A
order by A.xyz
group by A.xyz

If that doesn't work you can always split it into nested queries.

To accomplish the same thing with nested queries:

SELECT MAX(MyCount)
FROM (SELECT A.xyz, COUNT(A.xyz) AS MyCount FROM ATable A GROUP BY A.xyz)

 

[jgbishop]

Thanks guys!