CODEWITHSUNDEEP
mysqlsql
user636859
user636859

Reputation:

Returning a list and a count in SQL

I was asked this question the other day in a phone screen and I had trouble with it. Suppose I have the following table. This is a table of employees, and more than one employee can have the same supervisor.

+------------+--------------+------+
| Field      | Type         | Null |
+------------+--------------+------+
| name       | varchar(40)  | NO   |
| department | varchar(100) | NO   |
| supervisor | varchar(100) | NO   |
+------------+--------------+------+

What query would I write to return a list of supervisors and a count of how many employees have that supervisor?

Upvotes: 0

Views: 58

Answers (4)

MagoMerlano
MagoMerlano

Reputation: 1

Just do a row count grouped by supervisor:

SELECT supervisor, COUNT(*) as managed_employees_n 
FROM Employees  
GROUP BY supervisor

This works, assuming you have a hierarchy with 2 levels only: there's not a supervisors' supervisor.

In a real scenario those names must be transformed to IDs, otherwise grouping by name could lead to wrong results... what if you find yourself with two different supervisors both called "John Doe" ?

Upvotes: 0

Gorkk
Gorkk

Reputation: 1056

You would use something like this (assuming your supervisor column is not a reference to another table)

select supervisor, count(name)
from employees
group by supervisor ;

Upvotes: 1

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798686

That is called a "self join" and requires the use of table aliases.

SELECT a.*, COUNT(b.name) AS numsubs
  FROM employees AS a
  LEFT JOIN employees AS b
    ON a.name = b.supervisor
  GROUP BY b.supervisor

Upvotes: 0

zealot
zealot

Reputation: 247

select supervisor, count(*) as cnt
from emp
group by supervisor

Upvotes: 0

Related Questions