How to find the maximum count using mysql?

Question

Please let me know what is wrong with the below command

mysql> select max(count(*)) from emp1 group by name;
ERROR 1111 (HY000): Invalid use of group function

Answer 1

Try:

SELECT NAME, COUNT(*) as c FROM table GROUP BY name ORDER BY c DESC LIMIT 1

Answer 2

From the supplied code I understand that you wish to select the highest number of employees that share the same name.

The problem with your query is that you are trying to apply more than one level of aggregation in a single scope.

Try this:

SELECT MAX(Total) FROM (SELECT COUNT(*) AS Total FROM emp1 GROUP BY name) AS Results

...or this:

SELECT COUNT(name) FROM emp1 GROUP BY name ORDER BY COUNT(name) DESC LIMIT 1

Both queries return the same result, but their implementations are different.

Use whichever is the fastest for you or whichever you prefer.

Answer 3

I'd to the following (assuming I understand correctly what you want):

select c from
(
    select count(*) as c, name from emp1 group by name
) tmp
order by c desc limit 1

This selects the largest count from all counts by name. For example, if your table contains

Name
-----------------------
Test
Test
Hello
World
World
World

The inner select would create a "table" with this data

c         Name
----------------------
2         Test
1         Hello
3         World

The outer select would order this by c descending and select the first entry, which is 3.

This can be shortened to

select count(*) c from emp1 group by name order by c desc limit 1

Answer 4

You are asking "what is wrong with your statement". This is your statement:

select max(count(*))
from emp1
group by name;

I understand what you mean. But a SQL Compiler does not. The reason is simple. A given select can have only one group by clause. And your query is asking for two of them. The first is the group by name. The second is an aggregation on all those results.

The proper way to write your query (as you seem to intend) is using a subquery:

select max(cnt)
from (select count(*) as cnt
      from emp1
      group by name
     ) t

This is a perfectly reasonable solution that only uses standard SQL. Other answers have proposed the solution using the limit clause, which may be a bit more efficient.

Answer 5

You must select name to group by it, then use max() on the result of that as a subquery:

select max(count)
from (
  select
    name,
    count(*) as count
  from emp1
  group by name) x

I have formatted the query so you can see what's happening, rather than put it all on one line as you showed it. Btw the "x" at he fnf is a required alias for the subquery.

Answer 6

SELECT  MAX(name_count)
FROM
        (
        SELECT  name
                ,count(*) as name_count
        FROM    emp1 
        GROUP BY
                name
        )

Answer 7

In case the data happened to have multi occurrences for the max value the LIMIT 1 will not answer the question. In order to illustrate this, I used the WOLRD database sample from MySQL to answer this question.

Q) Return the list of the country(ies) that has the highest number of languages.

FIVE countries fit to have the same number of languages which is 12 namely

1. Canada
2. China
3. India
4. Russia
5. USA

Firstly, we need to create a VIEW to hold the max value (in this case is 12)

CREATE VIEW abc AS SELECT count(countrycode) AS total FROM 
countrylanguage GROUP BY countrycode 
ORDER BY COUNT(countrycode) DESC limit 1;

then use the view in the SELECT statement below:

SELECT `name`, COUNT(country.`name`) FROM country JOIN    
countrylanguage ON country.`code` = countrylanguage.countrycode 
GROUP BY country.`name` 
HAVING COUNT(country.`name`) = (SELECT total FROM abc) ;

Answer 8

you didn't need max here i think you want know the employee count

then use just count()
like this

select count(*) from emp1

but here another example for use max with count for example we need to know most author have many books

SELECT  book_author.`author_id`
,  COUNT(book_author.`book_id`)  AS books_count
 FROM book_author
GROUP BY book_author.`author_id`
HAVING   COUNT(book_author.`book_id`)=(SELECT MAX(t1.book_count) 
 FROM 
(SELECT COUNT(book_author.`book_id`)  AS book_count 
FROM  book_author
GROUP BY book_author.`author_id` )t1
)

Answer 9

 ***Example: 1***
 SELECT *
 FROM customer
 WHERE customer.ID IN
      (SELECT customer_id
         FROM (SELECT customer_id, MAX(cust_count)
                 FROM (SELECT customer_id,
                              COUNT(customer_id)
                                 AS cust_count
                         FROM `order`
                       GROUP BY customer_id) AS cust_count_tbl) AS cust_tbl);


***Example -2*** 

SELECT *
      FROM    customer
   LEFT JOIN
      (SELECT customer_id, COUNT(customer_id) AS cc
         FROM `order`
       GROUP BY customer_id
       ORDER BY cc DESC
        LIMIT 1) AS kk
   ON customer.ID = kk.customer_id
   WHERE kk.customer_id = customer.ID;