Written by Sai Sandeep Thota on March 14, 2013 in How to > Programming > SQL

How to get Max Count in MySQL

GET ALERTS:

Get our Latest updates delivered to your mailbox!

Yay! This is our 50th article and we are very happy with our readers support. Today I got a query from one of the user on How to get MAX COUNT in MySQL MAX(COUNT(*)). Actually, when I wrote this query in Oracle SQL, I got the output. Where as coming to MySQL I got an error.

I came up with a solution for MySQL, The syntax will look like:

SELECT MAX(Alias) FROM (SELECT COUNT(*) AS Alias FROM tbl GROUP BY field) AS derived_alias;

Lets elaborate this a little bit clearly. Here what we are exactly trying to do is, for example take a Company which has three departments. Each department has n number of employees.

First lets get the Count of Employees in each department.

SELECT DEPTNO, COUNT(*) FROM emp GROUP BY deptno;

So we got the total number of employees, grouped by each department.

+--------+----------+
| DEPTNO | COUNT(*) |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+

So we are trying to get the Department which is having the maximum number of employees. So lets see how the query looks like!

SELECT MAX(Members) FROM (SELECT COUNT(*) AS Members FROM emp GROUP BY deptno) AS Max_Emp;

In the above query we are using Alias names. We are defining COUNT(*) as Members and using in MAX. We are using the alias name Max_Emp as a derived table must have a alias name.

The output will look like:

+--------------+
| MAX(Members) |
+--------------+
|            6 |
+--------------+

So, we have fetched the maximum number of employees. But we don’t know which department is having maximum number of employees. So to get the Department we need to implement the following:

SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*) = (SELECT MAX(Members) FROM (SELECT COUNT(*) AS Members FROM emp GROUP BY deptno) AS Max_Emp);

The output will look like:

+--------+
| DEPTNO |
+--------+
|     30 |
+--------+

The query looks like this in Oracle SQL:

SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM EMP GROUP BY DEPTNO);

The Output will look like:

+--------+
| DEPTNO |
+--------+
|     30 |
+--------+

While I tried the same in MySQL I got the error Invalid use of Group By expression. If you have a better solution for the above query, Please let us know.

If you have any questions, you can always mail us at: admin@programmingunit.com

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: