how to display department name along with number of employe

Question

Hi frnds how to display department name along with number of employees for each department with asecending order?

Shahriar Nikkhah · Answer

Good luck DECLARE @tblDepartment AS TABLE ( [dep_id] Int , [DepartmentName] nVarChar(20) ) INSERT INTO @tblDepartment VALUES (1, 'Department 1' ) , (2, 'Department 2' ) , (3, 'Department 2' ) --- For Test --SELECT MT.* FROM @tblDepartment MT DECLARE @tblemployees AS TABLE ( [emp_id] Int , [ Name ] nVarChar(10) , [dep_id] Int ) INSERT INTO @tblemployees VALUES (1, 'Smith' , 1) , (2, 'Tim' , 1) , (3, 'Nik' , 3) --- For Test --SELECT ST.* FROM @tblemployees ST --- For Test SELECT MT.* , ST.* FROM @tblDepartment MT INNER JOIN @tblemployees ST ON MT.[dep_id] = ST.[dep_id] SELECT MT.[DepartmentName] , COUNT (*) AS [Emp Count ] FROM @tblDepartment MT INNER JOIN @tblemployees ST ON MT.[dep_id] = ST.[dep_id] GROUP BY MT.[DepartmentName] ORDER BY 1 ASC

Nitin Sontakke · Answer

I just googled your question and I found following: https://stackoverflow.com/questions/10779569/how-to-display-department-details-along-with-number-of-employees-for-each-depart?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa These are standard questions answered on database forums for decades. Just google and you will find the answer.

Shahriar Nikkhah · Answer

Hi Nitin I think the most important thing is YOU, you as in loving your job, you as in loving the technology, you as in loving to help people out for free, you...... The most important thing is that I see people like you helping out and that is what motivates me to help like you, your work is positive and so are you. Just keep up the good work you will always be noticed Sincerely Nik

Nitin Sontakke · Answer

@Shahriar, I am very well aware that the question is not directed towards me and there is no need for me to jump in. Having said that, by now you must have realized that the abondoning questions is a standard practise on this forum and I really hate it. Somehow, I just cannot comprehend that one has enough time and energy to visit the forum and post the question but when answers have poured in, have neither time nor the energy to just say 'Thank you' or accept the one of the replies as answer. What I hate most is our passion for technology, our willingness to help, our research in answering the question, our preparation of script and code, our time, our experiance, everything is being ignored and indirectly being insulted. Then why do I come and give answers here again? Because I just keep hoping that this poster might be different or now after few months, this poster might have changed, might have learned some decency. I just keep hoping that. And I say to myself, 'what if this poster is different, what if now this poster has changed, and will you not be doing injustice to some serious student' and then I keep on answering the questions. I am not been able to kill the sincerity / sensitivity within myself because someone else has done it. And then there will be no difference between me and them. Infact, I will not even be real 'me' then.

Shahriar Nikkhah · Answer

Hi Anjali Did you get your answer?

Jayesh Vaghasiya · Answer

DECLARE @Department AS TABLE (Dept_id Int,Dept_Name VarChar(32)) INSERT INTO @Department VALUES (1,'Sales') , (2,'Production') , (3,'Packing') DECLARE @Employees AS TABLE ( Emp_id Int ,Emp_Name VarChar(32) ,Dept_id Int ) INSERT INTO @Employees VALUES (1,'Jayesh' , 1) , (2, 'Patel' , 2) , (3, 'Niki' , 3) , (4,'Jayesh niki' , 1) , (5, 'Patel niki' , 1) , (6, 'Niki niki' , 3) SELECT Dept.Dept_Name , COUNT(*) AS Emp_Count FROM @Department Dept INNER JOIN @Employees Emp ON Dept.Dept_id = Emp.Dept_id GROUP BY Dept.Dept_Name ORDER BY Emp_Count ASC

Packiaraj Santhiyagu · Answer

Hi Anjali, can you provide your script?