how to find maximum occurences of distinct value?

[zensunni]

Say we have a couple of tables like so:

Departments_to_Employees
department_id
employee_id

Employee
employee_id

I want to find how many employees the department with the maximum amount of employees has.

On other words, I want to find the department with the most amount of employees, then find out how many employees are in that department.

Is there any way to do this using a single SQL statment? I've searched on google, but I haven't seen any articles that go over this specific issue.

[coolsti]

First of all, you are showing the wrong table structure for this kind of thing. A department has employees, yes, but it is the employee that belongs to a department, not a department that belongs to an employee. Changing the design like this will make queries easier. In other words:

CREATE TABLE `departments` (
`iddepartment` smallint(5) unsigned NOT NULL auto_increment,
`departmentname` varchar(50) default NULL,
PRIMARY KEY (`iddepartment`)
)
CREATE TABLE `employees` (
`idemployee` smallint(5) unsigned NOT NULL auto_increment,
`iddepartment` smallint(5) unsigned default NULL,
`employeename` varchar(50),
PRIMARY KEY (`idemployee`)
)

Now you can do something like this:

select t1.iddepartment,departmentname,count(*) as number_employees
from departments t1, employees t2 where t1.iddepartment=t2.iddepartment
group by t1.iddepartment order by number_employees desc limit 1

This will not give you more than one row returned if more than one department has the same amount of employees as the maximum. For that, you would need a more complicated query. But this will give you what you asked for, namely the maximum count of employees in all departments. In the above example, I also extract the department ID and the department name. You could also get a deliminated list of the employees in the department using a group_concat(employeename).

[zensunni]

First of all, you are showing the wrong table structure for this kind of thing. A department has employees, yes, but it is the employee that belongs to a department, not a department that belongs to an employee.

You assumed that the relationship was one-to-many. That would be easy. But, this scenario is a many-to-many relationship. An employee can have more than one department and a department can have more than one employee.

Department_to_employee is a lookup table.

[coolsti]

ah ok, I didn't realize that.

Then why would this not work?

select department_id, count(*) as number_employees
from Departments_to_Employees
group by department_id order by number_employees desc limit 1

[zensunni]

Never thought to put it together like that. Thanks!

[coolsti]

Just to let you know, there is a more complicated way of doing this, which can be used if you require a bit more from your select. In your case, you only want one row returned, which is for the department with the maximum value. On the other hand, if you wanted many rows returned, you would need the more complicated way of doing it.

Example: let us say you have another table that holds the number of sick days per employee, and you then wanted a query that shows the employee with the maximum number of sick days per department. Then your result would not have just one row, but many, one for each department.

Unfortunately, I cannot show you right now the solution to this, as I cannot remember it!!! Each time I need this myself I need to search through all my code for an example of how I did it, which I initially found via a search on the net. But it is possible!