Sql query for a specific condition
I have a table like:
empId empSalary empDept
1 45000 IT
2 40000 IT
3 50000 SALES
4 60000 SALES
5 75000 IT
6 80000 IT
7 25000 OPS
8 30000 OPS
9 55000 MARKETING
10 60000 MARKETING
I need to write a query like:
select empId where empSalary > avg(empSalary) for each empDept
Please help.
+3
source to share
2 answers
Try the following:
SELECT e.empId, e.empSalary, e.empDept
FROM employee e
INNER JOIN (SELECT e1.empDept, AVG(e1.empSalary) empSalary
FROM employee e1
GROUP BY e1.empDept
) a ON e.empDept = a.empDept AND e.empSalary > a.empSalary;
EDIT
SELECT e.empDept, COUNT(DISTINCT e.empid) noOfEmployees
FROM employee e
INNER JOIN (SELECT AVG(e1.empSalary) empSalary FROM employee e1) a ON e.empSalary > a.empSalary
GROUP BY e.empDept;
+2
source to share
There might be something with a subquery, something like this:
SELECT empID FROM TABLE t
WHERE empSalary > (SELECT AVG(empSalary) FROM TABLE WHERE empDept = t.empDept)
But can also be done with a JOIN, which probably works better:
SELECT empID FROM TABLE t
JOIN (SELECT empDept, AVG(empSalary) AS avgSalary FROM TABLE GROUP BY empDept) averages
ON t.empDept = averages.empDept
WHERE t.empSalary > averages.avgSalary
EDIT: The updated question requires something slightly different, as the first comment below suggests. Here the number of employees exceeds the total average salary, by department:
SELECT empDept, count(empID) FROM
(SELECT empID, empDept from TABLE
WHERE empSalary > (SELECT AVG(empSalary) FROM TABLE) aboveAverageEmployees
GROUP BY empDept
+1
source to share