By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,378 Members | 1,453 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,378 IT Pros & Developers. It's quick & easy.

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

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

Expand|Select|Wrap|Line Numbers
  1. select max(count(*))
  2. from buys
  3. group by v_id
bt i have the error aggregate function calls may not be nested
plzzz help
Apr 19 '08 #1
Share this Question
Share on Google+
6 Replies


P: 44
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 !
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 * FROM 
  2. (
  3.     SELECT v_id, COUNT(*) AS COUNT
  4.     FROM buys
  5.     GROUP BY v_id
  6. ) A
  7. ORDER BY 2 DESC
  8.  
Apr 19 '08 #2

P: 50
Probably you are looking for this !
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 * FROM 
  2. (
  3.     SELECT v_id, COUNT(*) AS COUNT
  4.     FROM buys
  5.     GROUP BY v_id
  6. ) A
  7. ORDER BY 2 DESC
  8.  

it doesnt work..it gives me syntax error near 1
plzz help
Apr 19 '08 #3

P: 92
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.
Apr 21 '08 #4

ganeshkumar08
P: 31
Expand|Select|Wrap|Line Numbers
  1. select max(cnt) from 
  2. (
  3. select count(*) as cnt
  4. from dbo.TrendCLUBS
  5. group by Club_HomeColor
  6. ) a
Now try the above one....
Apr 21 '08 #5

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

If your database is sql server then it should work !
Apr 21 '08 #6

P: 1
tested.

Expand|Select|Wrap|Line Numbers
  1. select max(cnt) from
  2. (
  3. select count(*) as cnt
  4. from dbo.TrendCLUBS
  5. group by Club_HomeColor
  6. ) a
is good and works fine with SQL Server.
Jan 31 '13 #7

Post your reply

Sign in to post your reply or Sign up for a free account.