SQL query to return salaries greater than average salary-CodePudding

Consider a table EMPLOYEE with columns empid, deptid and salary to extract all employees who have salaries higher than the avg. of their department

I tried it two ways - 1st way is this:

Select empid, deptid, salary
From employee
Group by empid, deptid, salary
Having salary > (Select avg(salary) From employee)

This returned a correct output.

But

Select empid, deptid, salary, avg(salary) average
From employee
Group by empid, deptid, salary
Having salary > avg(salary)

returned no output as well as no error it was black output.

Software used is Microsoft SQL Server

I wanted to know why second output is not returning any output?

CodePudding user response：

The reason you get no results in your second query is because you are grouping by salary, therefore all salaries within that group will be the same, as such the average will be the same. So if the salary within a particular group is 50k, the average will be 50k, and 50k is not greater than 50k, it is equal.

I'd also assume that empId is your primary key, so grouping by this is (with no other joins) is almost always going to be pointless because by definition you have one row per group.

Your first query also isn't right, you would need to filter the subquery by department to ensure you were only comparing the average within that particular employees department, e.g.

Select empid,deptid,salary
From employee AS e
Group by empid,deptid,salary
Having salary>(Select avg(salary) FROM employee AS e2 WHERE e2.deptId = e.Deptid)

However, since you have moved the aggregate to the subquery, the GROUP BY and HAVING are not necessary and this can be simplified to:

Select empid,deptid,salary
From employee AS e
Where salary>(Select avg(salary) FROM employee AS e2 WHERE e2.deptId = e.Deptid);

I would however be inclined to do this with a windowed function:

SELECT  e.EmpID, e.DeptId, e.Salary, e.DeptAvg
FROM    (   SELECT  e.EmpID, 
                    e.deptid, 
                    e.Salary,
                    DeptAvg = AVG(e.Salary) OVER(PARTITION BY e.deptid)
            FROM    employee AS e
        ) AS e
WHERE   e.Salary > e.DeptAvg;

CodePudding user response：

First of all you for each row SQL calculates AVG for particular record so each AVG(salary) will be equal to salary of particular employee. Here you looking for two operations, first find average salary of individual department and second is for particular department find list of employees which have greater than avg salary of that department

;WITH cteavgsalary AS 
(SELECT deptid,
           AVG(salary) AS avgsalary
    FROM employee
    GROUP BY deptid)

SELECT e.empid,       e.deptid,       e.salary,       cte.avgsalary
FROM employee e
INNER JOIN cteavgsalary cte ON e.deptid = cte.deptid
AND e.salary > cte.avgsalary

Here cteavgsalary will be table which will hold record of department wise average salary and then you making inner join of that CTE on particular department Id

It will provide you exact output you looking for.

CodePudding user response：

This gives you the salary and avg by dept.

Select deptid, avg(salary) as dept_avg
From employee
Group by deptid

This gives you the ones above avg

Then you join back to find the ones you want.

select e.empid, e.deptid, e.salary
FROM emplyee e
JOIN (
  Select deptid, avg(salary) as dept_avg
  From employee
  Group by deptid
) sub on sub.deptid = e.deptid
WHERE e.salary < sub.dept_avg