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;

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.

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

This can be achieved using cte and window function using the avg function:

with cte_highest
as

(
select firstname,dept,salary,avg(salary) over (partition by dept) as avg_sal
from employee
)
select firstname,dept,salary,avg_sal from cte_highest
where salary > avg_sal;