-7

I have an Employee table with columns : Id, Name, Manager_Name

I need to list: count of employees under each manager.

How to form a simple sql query for achieving result?

Note: There may be two managers with same name.

Table:- create table employee_test (Id int , Name VARCHAR(100), Manager_Name varchar(100));

Input: 

 ID NAME   MANAGER_NAME
 -- ------ ------------
  1 deep   hari
  2 mitra  hari
  3 hari   kishan
  4 kirti  kishan
  5 kishan amit
  6 jumeet hari
  7 fareed deep
  8 stuti  kishore

My Attempt:-

SELECT m.Name as ManagerName, count(e.Name) as employeesCount FROM employee_test e INNER JOIN employee_test m ON e.Manager_Name = m.Name group by m.Name

My Output:-

 MANAGERNAME EMPLOYEESCOUNT
 ----------- --------------
 kishan                   2
 hari                     3
 deep                     1

But it does not take care of other manager's employee count?

UPDATE:- Question was little unclear to me too as being exactly asked by an interviewer, request to close the question.

Deepak S
  • 1,544
  • 3
  • 15
  • 33
  • You are joining the table with itself. Then **there is a foreign key**. Please add it to the example. – The Impaler Apr 13 '18 at 14:50
  • Please format code in code blocks, like you did the tables. Always check the formatted version of your post. If ids identify people, why are you using names to identify managers? Do names also identify managers? Find out about what PKs/UNIQUEs you should declare. Also find out about the FK(s) you should be declaring--when values for a list of columns must appear elsewhere as PK/UNIQUE. Please also read & act on [mcve]--if your input table isn't also formatted code, give code for them. Google stackoverflow help re http://sqlfiddle.com. – philipxy Apr 14 '18 at 18:29
  • Where are amit & kishore in the results? [mcve] Why are you asking for rows that can be made from an e row & an m where the manager name equals the employee name? Why aren't you just grouping manager names? The only reason you would self-join is if names are identifiers (which must be so anyway because that's how you associate employees & managers) & you want to get manager ids. But this table doesn't give ids for all manager names so you couldn't get that from a self-join for this table. And anyway that self-join is not the table you would group. Explain why you wrote your code as you did. – philipxy Apr 14 '18 at 18:57
  • 1
    If "There may be two managers with same name" then it is not possible to get "count of employees under each manager" with this table, because it doesn't give manager id for an employee. You can only get count of employees under each manager name. – philipxy Apr 21 '18 at 05:00
  • You can delete your posts, click on 'delete'. To "close" means answers are blocked from being posted until people vote to "reopen", although people can still vote & comment. You can google protocol & help questions using site:stackexchange.com & site:meta.stackoverflow.com. You can post questions about protocol & help at those sites. You can flag a post to message a moderator. But are you able to correct/clarify your question?--Should MANAGER_NAME be MANAGER_ID? Should every MANAGER_ID value be an ID value? Should there be a FK from MANAGER_ID to ID? Can MANAGER_ID be NULL to say no manager? – philipxy Apr 23 '18 at 05:14
  • @philipxy Yes I feel that MANAGER_NAME should be MANAGER_ID referring as foreign key to ID and it can be NULL to say no manager, but since I got the question asked as is, I did not ask more clarification from the interviewer. But will surely take care of the points you mentioned since unclear question has rather brought down my SO score. I would delete the question rather. – Deepak S Apr 23 '18 at 06:13
  • If you clean up your content & format then I can upvote, and if you comment on the answers then the answerers can correct them & maybe upvote, and you can accept and get some rep too. Notice that the answers *now* are wrong, since no query can do what you are currently asking, see my comment. But be clear--eg, what columns are in the result? Remember, [mcve]. (You only need a self-join if you want manager ids *and* names.) Anyway, good luck. – philipxy Apr 23 '18 at 06:29

2 Answers2

0

I imagine you want this:

select Manager_Name, count(Name)
from Employee
group by Manager_Name
MJH
  • 1,710
  • 1
  • 9
  • 19
  • I would use `count(*)` instead of `count(name)` but I think this is correct. There's no need for a self join. – The Impaler Apr 13 '18 at 14:53
  • @The Impaler Normally, I would be inclined to agree, but what if there are managers with no staff? – MJH Apr 13 '18 at 15:00
0

In addition to the response by MJH, if there can be two managers with the same name, there needs to be a way to differentiate them.

Say for instance you have the following (in SQL Server):

create table Employee (Name VARCHAR(100), Manager_Id INT)
create table Managers (Id INT, Name VARCHAR(100))

SELECT d.Manager_Name, d.employeesCount 
FROM(
    SELECT m.Id, m.Name as Manager_Name, count(e.Name) as employeesCount
    FROM Employee e
    INNER JOIN Managers m ON e.Manager_Id = m.Id
    group by Id, m.Name 
) d
JAmor
  • 1
  • 1
  • 1
    It seems to me he's talking about a single table since he says "self join". However, there's no foreign key whatsoever. I don't get it. – The Impaler Apr 13 '18 at 13:53
  • 1
    @TheImpaler, you don't _need_ fk's to join. (Their main purpose is to ensure data consistency.) – jarlh Apr 13 '18 at 14:35
  • He's joining the table with itself. Then, there is an "undeclared" foreign key there. It needs to be added for the example to make sense. I don't think it's two separate tables. – The Impaler Apr 13 '18 at 14:51
  • @TheImpaler Constraints are not needed to make sense of (interpret) (query or update) a database. They get the DBMS to reject know invalid states/situations. I'd agree that sometimes an SQL/English query only makes sense given that certain constraints hold. Then a query that called a partial function (eg a scalar subquery) outside its domain would not make sense but others still would. But here there *isn't* a FK from MANAGER_NAME to NAME since amit & kishore are not whatever kind of NAME the table gives. The question does not require a self-join! Nevertheless its self-join means something. – philipxy Apr 14 '18 at 19:04
  • yup, that's what I was talking about. The lack of FK constraints allowed an impossible example. If Deepak had added the constraints, the question would be much better formulated. – The Impaler Apr 15 '18 at 17:18
  • @TheImpaler I don't know what you are trying to say. I don't know what "that" is & I cannot make sense of "The lack of FK constraints allowed an impossible example". 1st--see my new comment above--the question can't be answered--though not because of FKs. It could be answered if it wanted manager *names* or if it gave managers' ids not names or managers didn't share names. I explained how certain specifications rely on a FK, but the suggested corrected questions have no such specification. Nor need a join, nor FK. And we have no other reason to think the FK holds & it contradicts the data. – philipxy Apr 21 '18 at 06:11