Question 20408 in Mysql Question posted in
The official MySQL documentation can be found here.

Using mySQL how to write query on "show the group(s) with maximum/minimum employees in it"?

So I can’t remember how exactly I can show all groups with MAX/MIN ppl in them. Say there is a table with columns Id, name, role(employee, employer), groupName(a1, b2, c3…) and I need to list MAX/MIN number of the employees in each group.

   SELECT `groupName`, COUNT(*)
 FROM Table1
 WHERE ROLE = 'employee'
 GROUP BY `groupName`
ORDER BY COUNT(*) DESC;

I need to find MAX/MIN number of employees in each group and there is a lot of groups.
But I guess what I really wonder is how can you select only maximum count and minimum count. Say in group 1a there is 200 employees and in 2b, 3c there is only one employee. Of course there is other groups but they are somewhere in between, and I need to get in my report only MAX and only MIN.

Tags:

2

Answers


  1. 0

    If you want the count for each group, you can do the following:

    SELECT groupName, COUNT(id) AS employee_count
FROM table1
WHERE role = 'employee'
GROUP BY groupName
ORDER BY employee_count DESC;

    The MIN or MAX only makes sense to use when you’re actually finding the min and max of a numerical column (or date, etc.). If you want to count employees per group, you can just use COUNT.

    If you need to show the group whose count is the maximum (or minimum) of all the groups, this is a slightly different question.

    Login or Signup to reply.
  2. 0

    If you really want to find the maximum number of employees for each group indeed, then the question does not make sense, because each group has a single number of employees (ex. group1 has 6 employees, group2 has 3 employees, etc.). So, in this answer I will presume that you want the minimum employee number and maximum employee number of each group.

    If you want to get the min/max employee number in each group, then you can achieve this as below:

       SELECT `groupName`, COUNT(*), MIN(Id), MAX(Id)
 FROM Table1
 WHERE ROLE = 'employee'
 GROUP BY `groupName`
ORDER BY COUNT(*) DESC;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search