How to get department Name of Employees Total Maximum Salary

Sunny Kathariya

10y1.6k

I Wrote Down This Query:

select d.Name as "Department",sum(e.Salary)as "Salary" from Employee e, Department d where d.Dept_ID=e.Dept_ID group by d.Name

o/p

But i want to That Department Which is Total Salary is Highest then other in output there is total salary of employees is Display of each Department but i want to display only Highest Total Salary Department Like '.Net' because its Total Salary higher Then Other

This Is my Employee Table

This is My Department Table

15 Replies

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

Praveen Sreeram10y ago
Accepted answer
Here is the updated query..
select
EmployeeData.[Total Salary] from
(
select
d.Name as "Department",
sum(e.Salary)as "Total Salary",
rank() over(order by sum(e.Salary)desc)as "Rnk"
from Employee e, Department d
where d.Dept_ID=e.Dept_ID group by d.Name
) as EmployeeData
where EmployeeData.Rnk=1
Hope it helped.
Thanks,
Prawin
+1
Sunny Kathariya10y ago
Yes bro.. @C#corner is very fine platform for learner like me and also it is also a way for developers like you to sharing their knowledge and expierience with us Thanx to C#corner and to you @prawin and also thanx to developer like you for supporting people like me and Helping us
0
Praveen Sreeram10y ago
Sure dude! Glad that it was helpful.
Let's thank @CsharpCorner for providing the platform :-)
Thanks,
Prawin
0
Sunny Kathariya10y ago
oh Bro U r a such a good Guy thank you so much now i am felling very confident because of you i am afraid of coding but now i am felling relax thanx dear one last thing will you be my Mentor? and please tell me how to contact you if i need ur your help @Prawin
0
Praveen Sreeram10y ago
Another important point I forgot to update is that.. Let's say, you have a requirement of getting the Top 3 highest salaries for each department, then Ranking Functions come very handy. You need to use the Ranking Functions along with Partitions
Thanks,
Prawin
0
Praveen Sreeram10y ago
Sure. I appreciate that you gave a try and I am happy that You learned something :-)
I will be post the updated query that returns you only the highest value in 15 minutes.
Thanks,
Prawin
0
Sunny Kathariya10y ago
Hello @Prawin i have tried RANK function and also i am much clear about it thanx for suggesting me this thing but also i am getting a little confusion i have wrote down this query
select d.Name as "Department",sum(e.Salary)as "Total Salary",rank() over(order by sum(e.Salary)desc)as "Rnk" from Employee e, Department d where d.Dept_ID=e.Dept_ID group by d.Name
its give whole row with rank column but i want to only highest value record and with out Rank column please help me how it is possible
0
Praveen Sreeram10y ago
Sure Sunny,
All the best bro. if you run into problems, please let me know.
Thanks,
Prawin
0
Sunny Kathariya10y ago
Thank You Very Much @Prawin you treat me like a brother or friend this thing i really very like in you now i am gonna try it myself but i request to you if possible then improve me like this i need it very much because i am a beginner in coding and i am learning different and new thing but i dont know how to learn it if you guys help me i am sure achive my goal thanx a lot again
0
Praveen Sreeram10y ago
Hi Sunny,
I suggest you to use Ranking functions. If you still cannot find an answer your self, I will provide you with the query.
One of the best ways to improve yourselves is to resolve problems on your own.
Thanks,
Prawin
+1
Sunny Kathariya10y ago
Yes freinds i want only highest total salary department @Vinay's answer is working proparly but @vasanth said top make poor performance so what is second way because @Vasanth's query gave me whole row but i just want to only one highest row Thank You All
0
Praveen Sreeram10y ago
Yes, Top is NOT suggested.
But, Vasanth, the question is get only one..the top 1. Your query will result all.
Thanks,
Prawin
0
Vasanth Natarajan10y ago
try this....
select d.dept as Department,sum(e.Salary )as Salary from Employee e,Department d
where d.ID=e.ID group by d.dept order by 1 asc;
Kindly never and ever use the "top" in SQL query, its make the(Poor) Performance issues
0
Praveen Sreeram10y ago
Hi sunny,
There are multiple ways to achieve your requirement. The best way is to use Ranking functions.
There are four ranking functions. Please go through the following link to understand about the ranking functions.
https://msdn.microsoft.com/en-in/library/ms189798.aspx
So, basically use ranking functions to rank the deparments based on the salaries. You need to use one of the ranking functions along with the query that you have already written.
I can make changes to your query and provide an answer. However, I don't want to do that. Rather, I would like you to explore about these Ranking functions and implement the requirement so that you get hands-on on these and improve your knowledge.
If you run into any issues, please let me know.
Thanks,
Prawin
+1
Vinay Singh10y ago
Hi Try this query
select top1 d.Name as "Department",sum(e.Salary) as "Salary"
from Employee e,Department d where d.Dept_ID=e.Dept_ID
group by d.Name order by sum(e.Salary) desc
+1