Click here to Skip to main content
15,175,525 members
Please Sign up or sign in to vote.
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

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
   
Comments
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
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
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
Comments
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!
SELECT TOP(1) name,dept,SUM(salary) AS sal FROM employee GROUP BY name,dept ORDER BY sal DESC
   
v3
Comments
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
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
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
   
Comments
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
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
Comments
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.
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
   
Comments
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)




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