15,175,525 members
1.00/5 (1 vote)
See more:
Name Salary Dept
John 40,000 IT
Sam 50,000 CS
John 30,000 IT

I need a sum like this

Name salary dept
John 70,000 IT
Sam 50,000 CS

then maximum salary

so , the answer will be like this...
Name Salary Dept
John 70,000 It

In a single query

What I have tried:

`select max(salary) from employee  where salary=(select name, sum(salary)  from employee   group by name)group by name,salary `
Posted
Updated 21-Apr-21 3:26am

## Solution 1

GROUP BY doesn't work like that - the more columns you add to the GROUP list, the more rows you get, not the fewer. See this: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^] - it explains GROUP by and aggregate functions.

What you want is to use GROUP BY and JOIN:
SQL
```SELECT a.[Name], a.[Salary], a.Dept
FROM MyTable a
JOIN (SELECT MAX(Salary) As Highest, Dept FROM MyTable GROUP BY Dept) b
ON a.Dept = b.Dept && a.Salary = b.Highest```
10923679 28-Apr-17 15:14pm

It did work for maximum salary but it didnt work to add the salary if it is for the same person like i mentioned in the above example
SUM(salary) is not used

## Solution 5

The solution you need is a combination of all of the above, it only gets more complicated because the salaries need to be summed first (probably not the best database design to be honest).

Unfortunately SQL Window functions (like `RANK()`) cannot be used in a `WHERE` clause so we need another sub-query or CTE.

This works:
SQL
```;WITH rankings AS
(
SELECT [Name], Dept, TotSalary, RANK() OVER (PARTITION BY Dept ORDER BY TotSalary DESC) as r
FROM (	SELECT [Name], Dept, SUM(Salary) AS TotSalary
FROM Employee GROUP BY Dept, [Name]) q
)
SELECT [Name], Dept, TotSalary FROM rankings where r = 1```
This version first sums the salary by department and name (in case people with the same name are in different departments - there's a hint regarding the table schema by the way).

It then RANKs the salaries because, as @Maciej-Los stated, more than one employee could earn the same amount (although @OriginalGriff's solution has a similar effect due to the WHERE clause).

Finally all of that goes into the CTE (it could have been a sub-query) so that we can pick out only the top ranking total salary.

[EDIT - the comments to Solution 3 made me re-read the question and I'v realised that you don't want the highest salary by department, just the highest salary overall. To get that just remove the partition by Dept
SQL
```;WITH rankings AS
(
SELECT [Name], Dept, TotSalary, RANK() OVER (ORDER BY TotSalary DESC) as r
FROM (	SELECT [Name], Dept, SUM(Salary) AS TotSalary
FROM Employee GROUP BY Dept, [Name]) q
)
SELECT [Name], Dept, TotSalary FROM rankings where r = 1```
v2

## Solution 2

You should use one of ranking function[^]: RANK()[^] Why? Because there can be more than one employee with the highest salary.

Try this:
SQL
```SELECT a.[Name], a.[Salary], a.[Dept]
FROM employee a INNER JOIN
(
SELECT [Name], [Dept], [Salary], RANK() OVER(ORDER BY [Salary] DESC) AS [Rank]
FROM employee
) as b ON a.[Salary] = b.[Salary] AND a.[Name] = b.[Name] AND a.[Dept] = b.[Dept]
WHERE b.[Rank] = 1```
v3
CHill60 4-May-17 6:28am

OVER(ORDER BY [Salary]) should be OVER(ORDER BY [Salary] DESC) ;-)
Maciej Los 4-May-17 14:39pm

Good point!

## Solution 6

`SELECT TOP(1) name,dept,SUM(salary) AS sal FROM employee GROUP BY name,dept ORDER BY sal DESC`
v3
CHill60 4-May-17 9:00am

As I said in the comments to your previous answer..."And what if there is more than one person with the same highest salary? (See the words in Solution 2)".
Do not post multiple solutions to a single question - at best it is confusing, at worst it is seen as rep-point hunting. Use the Improve solution link if you want to change the approach, or at the very least delete your original solution

## Solution 7

If situation like you have both employee details and department in same table and you have to fine the highest paid employee from each department then, you can try below solutions

```EmployeeId	EmployeeName	Department	   Salary
1	         Neeraj	        Dot Net	       45000
2	         Ankit	        Java	       5000
3	         Akshay    	    Java	       6000
4	         Ramesh	        Dot Net	       7600
5	         Vikas	        Java	       4000
7	         Neha	        Php	           8500
8	         Shivika	    Php	           4500
9	         Tarun	        Dot Net	       9500```

Then you can solve it by using below solutions

Solution - 1
```SELECT t.EmployeeName,t.Department,t.Salary FROM(SELECT MAX(Salary) AS TotalSalary,Department FROM Employee GROUP BY Department) AS TempNew
Inner Join Employee t ON TempNew.Department=t.Department
and TempNew.TotalSalary=t.Salary
ORDER BY t.Department ASC```

Solution -2
```;WITH EmployeeDetails AS (
SELECT EmployeeName, Department, DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS SalaryRank, Salary
FROM Employee
)
SELECT EmployeeName, Department, Salary FROM EmployeeDetails WHERE SalaryRank=1```

OUTPUT
```EmployeeName	Department	Salary
Neeraj	         Dot Net	45000
Akshay	         Java	    6000
Neha	         Php	    8500```
v4

## Solution 8

This will give the answer you are looking for. Enjoy.
```SELECT
NAME,
SALARY,
DEPT
FROM
(SELECT
ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUM,
NAME,
SALARY,
DEPT
FROM
(SELECT
NAME,
SUM(SALARY) AS SALARY,
DEPT
FROM
Employee
GROUP BY
NAME,
DEPT
)A
)B
WHERE
ROWNUM = 1```
CHill60 28-Oct-17 12:54pm

The OP got the answer they were looking for several months ago. You have added nothing new to the thread

## Solution 9

If you have any same salary for the 2 or more employees The Rank function will skip the rank The best solution is using Dense_Rank() :

Select * from (
SELECT EmployeeName, Department,salary , DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS DenseRank
FROM Employee
)as Results

WHERE DenseRank =1 ;
v2
Richard Deeming 14-May-20 7:32am

`RANK` will NEVER skip the first value.

If you were looking for the second highest paid employee, then it would be necessary to use `DENSE_RANK`. But this homework assignment was looking for the highest paid employee, so `RANK` is perfectly fine, and solutions 1 and 2 are correct.

## Solution 13

In case you have multiple table for companies, employees, salaries

select a.employee_name, a.company_name, a.salary from
(
select e.employee_name, c.company_name, s.SALARY_AMOUNT salary from EMPLOYEES e
join COMPANY c on e.COMPANY_ID = c.COMPANY_ID
join SALARIES s on e.EMPLOYEE_ID = s.EMPLOYEE_ID
) a
join
(
select c.company_name, max(s.SALARY_AMOUNT) salary from EMPLOYEES e
join COMPANY c on e.COMPANY_ID = c.COMPANY_ID
join SALARIES s on e.EMPLOYEE_ID = s.EMPLOYEE_ID
group by company_name
) b
on a.company_name = b.company_name && a.salary = b.salary
CHill60 21-Apr-21 10:42am

You have added nothing new to the thread - this is essentially the same as solution 1 but with table joins. The OP did not specify multiple tables

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 291 CPallini 160 M Imran Ansari 104 Richard MacCutchan 60 Luc Pattyn 60
 OriginalGriff 4,559 Richard MacCutchan 1,558 CPallini 1,143 Luc Pattyn 955 CHill60 925

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900