Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Unanswered: Problem of max(count(*))

    Can someone tell me why the following sql statement cannot retrieve the
    maximum count of the record:

    SELECT col, max(count(*)) FROM table
    GROUP BY col

    And also, can I use one sql statement to retrieve the maximum count of the
    record? If not, how?

    Thank you more!!

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Two group functions

    Hello,

    you use two group function MAX and COUNT in one statement, but there is only on group by function. So the error will be "no group ..."

    The count is already the maximum in the group col, but if you need the
    maximum of all col the use


    SELECT max(count(*)) FROM table

    Hope that helps

    Manfred Peter
    Alligator Company Software GmbH
    http://www.alligatorsql.com

  3. #3
    Join Date
    May 2003
    Posts
    45

    Re: Problem of max(count(*))

    i dont know what you exactly want to retrive using max(count(*))

    actually count(*) will return only one value which is maximum.

    so i think the following should work for u


    SELECT col, count(*) FROM table
    GROUP BY col

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    the only way max(count()) will have any meaning, is by getting more then 1 row from count().....
    Only that case the max function will have any relevance.

    example:
    select count(*) from emp return one row. This is always the max
    select count(*), deptno from emp group by deptno will return 3 rows. Now I have a situation in which a max would have relevance.


    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    Oct 2003
    Posts
    3

    Re: Problem of max(count(*))

    SELECT max(count(*)) FROM table
    GROUP BY col
    /*that's all folks!*/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •