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

max count

P: 50
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
Apr 19 '08 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 700
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
maybe like that

Expand|Select|Wrap|Line Numbers
  1. select max(column) from (select count(*) as column from buys group by v_id) t;
  2.  
Apr 19 '08 #2

P: 50
maybe like that

Expand|Select|Wrap|Line Numbers
  1. select max(column) from (select count(*) as column from buys group by v_id) t;
  2.  

thx a lot it works...but can u help me some more plzz...i need to get the corresponding v_id where the count is maximum..
i tried doing this
Expand|Select|Wrap|Line Numbers
  1. select t.vid,max(c) from (select vid,count(*) as c from buys group by vid) t;
bt i get the error
ERROR: column "t.vid" must appear in the GROUP BY clause or be used in an aggregate function
i tried using top 1 bt i dnt knw how to use it..
i did this
Expand|Select|Wrap|Line Numbers
  1. select TOP 1 t.vid,max(c) from (select vid,count(*) as c from buys group by vid) t group by t.vid;
bt i get a syntax error near 1..

plzz help
Apr 20 '08 #3

Expert 100+
P: 700
thx a lot it works...but can u help me some more plzz...i need to get the corresponding v_id where the count is maximum..
i tried doing this
Expand|Select|Wrap|Line Numbers
  1. select t.vid,max(c) from (select vid,count(*) as c from buys group by vid) t;
bt i get the error
ERROR: column "t.vid" must appear in the GROUP BY clause or be used in an aggregate function
i tried using top 1 bt i dnt knw how to use it..
i did this
Expand|Select|Wrap|Line Numbers
  1. select TOP 1 t.vid,max(c) from (select vid,count(*) as c from buys group by vid) t group by t.vid;
bt i get a syntax error near 1..

plzz help
I don't think there is TOP option in postgres, I guess you mean LIMIT clause.
Here is the solution, probably not the best one but I'm to tired to look for better one
Expand|Select|Wrap|Line Numbers
  1. select vid from (select vid ,count(*) as k from buys group by vid)as t where k=(select max(k) from (select count(*) as k from buys group by vid) l);
  2.  
Apr 20 '08 #4

P: 1
select t.vid,max(c) from (select vid,count(*) as c from buys group by vid) t group by vid limit 1;
Feb 13 '14 #5

Post your reply

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