getting max count - error: error aggregate function calls may not be nested

[poopsy]

hi all
i have the following query, i need to get the max count from a table

select max(count(*))
from buys
group by v_id

bt i have the error aggregate function calls may not be nested
plzzz help

[siva538]

hi all
i have the following query, i need to get the max count from a table

select max(count(*))
from buys
group by v_id

bt i have the error aggregate function calls may not be nested
plzzz help

Probably you are looking for this !

SELECT TOP 1 * FROM 
(
    SELECT v_id, COUNT(*) AS COUNT
    FROM buys
    GROUP BY v_id
) A
ORDER BY 2 DESC
 

[poopsy]

Probably you are looking for this !

SELECT TOP 1 * FROM 
(
    SELECT v_id, COUNT(*) AS COUNT
    FROM buys
    GROUP BY v_id
) A
ORDER BY 2 DESC
 

it doesnt work..it gives me syntax error near 1
plzz help

[deric]

hi all
i have the following query, i need to get the max count from a table

select max(count(*))
from buys
group by v_id

bt i have the error aggregate function calls may not be nested
plzzz help

You created another thread with the same content as this thread.. You need to review the posting guidelines of this site again. :D

Let's get back to your original script. You cannot use the aggregate function Count() as a parameter to some other aggregate functions like Max(). Anyhow, because your statement doesn't have Where clause and the count function does not specify a particular column/value to count, it will just output rows with always the same value. So, what particular count in your table are you trying to get?
If you can provide sample rows and your expected output, that would be easy for me to help you.

[ganeshkumar08]

select max(cnt) from 
(
select count(*) as cnt
from dbo.TrendCLUBS
group by Club_HomeColor
) a

Now try the above one....

[siva538]

it doesnt work..it gives me syntax error near 1
plzz help

If your database is sql server then it should work !

[FourSeasons]

tested.

select max(cnt) from
(
select count(*) as cnt
from dbo.TrendCLUBS
group by Club_HomeColor
) a

is good and works fine with SQL Server.