how to display department name along with number of employe

Anjali Khan

8y1k

Hi frnds

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

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah8y ago
Accepted 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
+2
Shahriar Nikkhah8y ago
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
+1
Packiaraj Santhiyagu8y ago
Hi Anjali, can you provide your script?
0
Nitin Sontakke8y ago
@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.
+1
Shahriar Nikkhah8y ago
Hi Anjali
Did you get your answer?
+1
Jayesh Vaghasiya8y ago
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
+1
Nitin Sontakke8y ago
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.
+2