Click here to Skip to main content
15,899,679 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Empid Deptno Sal
1 1 1000
1 2 2000
2 3 3000
3 4 4000
4 1 5000
2 2 6000

What I have tried:

I used some aggregate and ranking functions
Posted
Updated 31-Jul-18 3:21am

First off, your data is bad: no department has more than two employees in that data, and some employees appear to be pulling a salary from two departments, which is ... um ... odd.
If you assume that you meant "more than one" employee to a Department, then it can be done very easily.

Start by grouping employees together by department: that's trivial, it's just a GROUP BY statement with a HAVING clause to restrict it to 2 or more employees:
SQL
GROUP BY DeptNo 
HAVING COUNT(EmpId) >= 2
Then you can apply your aggregate function to the groups:
SQL
SELECT DeptNo, SUM(Sal) AS Total FROM Employees
GROUP BY DeptNo 
HAVING COUNT(EmpId) >= 2
And that gives you the result you need:
DeptNo  Total
1        6000
2        8000
 
Share this answer
 
You first need to get the COUNT of employees for each department. Once you have those figures you can create a SUM query for each department number.
 
Share this answer
 

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