May 23, 2022 10:36 am
How to write an SQL query to find salary greater than average salary.
Here is the Answer.
mysql> select * from MyTable; +------+------------+------+ | id | d2 | Ref | +------+------------+------+ | 500 | 2010-12-22 | 3 | | 500 | 2011-12-22 | 2 | | 500 | 2012-12-22 | 1 | | 700 | 2011-12-22 | 1 | +------+------------+------+ 4 rows in set (0.00 sec) mysql> select * from MyTable having Ref > avg(Ref); +------+------------+------+ | id | d2 | Ref | +------+------------+------+ | 500 | 2010-12-22 | 3 | +------+------------+------+ 1 row in set (0.00 sec) mysql> select * from MyTable having Ref > (select avg(Ref) from MyTable); +------+------------+------+ | id | d2 | Ref | +------+------------+------+ | 500 | 2010-12-22 | 3 | | 500 | 2011-12-22 | 2 | +------+------------+------+ 2 rows in set (0.02 sec) mysql> select * from MyTable where Ref > (select avg(Ref) from MyTable); +------+------------+------+ | id | d2 | Ref | +------+------------+------+ | 500 | 2010-12-22 | 3 | | 500 | 2011-12-22 | 2 | +------+------------+------+ 2 rows in set (0.00 sec) mysql> select *,avg(Ref) from MyTable having Ref > avg(Ref); +------+------------+------+----------+ | id | d2 | Ref | avg(Ref) | +------+------------+------+----------+ | 500 | 2010-12-22 | 3 | 1.7500 | +------+------------+------+----------+ 1 row in set (0.00 sec) Share Improve this answer Follow answered May 23, 2022 at 17:26
3 Replies
May 23, 2022 10:47 am
Hello , Check the attached document for
SQL query to find salary greater than average salary
Here I have shared the two methods.
May 23, 2022 10:50 am
Thanks for your help. I was exactly looking for the same. The methods you have provided helped me a lot.
May 23, 2022 10:54 am
Here is another approach to find the employees who are earning more salary than the avg salary from the employee table.
SELECT empno, ename, deptnum FROM emp e WHERE sal > (SELECT AVG(sal) FROM emp e1 WHERE e.deptnum = e1.deptnum GROUP BY deptnum) ORDER BY deptnum;