'How to determine salaries greater than the average salary

Assume I have the following table

id  name city   salary  dept

and I want to select all salaries which are greater than the average salary

sql


Solution 1:[1]

Try something like this:

SELECT salary WHERE salary > (SELECT AVG(salary) FROM *)

Solution 2:[2]

Assuming it's mysql, only the below two work. (I used a temp table so the names are different from yours)

select * from b  where ref > (select avg(ref) from b);
select * from b  having ref > (select avg(ref) from b);

This doesn't - select * from b having ref > avg(ref);

Some queries I tried - 

mysql> select * from b;
+------+------------+------+
| id   | d2         | ref  |
+------+------------+------+
|  300 | 2010-12-12 |    3 |
|  300 | 2011-12-12 |    2 |
|  300 | 2012-12-12 |    1 |
|  400 | 2011-12-12 |    1 |
+------+------------+------+
4 rows in set (0.00 sec)

mysql> select * from b  having ref > avg(ref);
+------+------------+------+
| id   | d2         | ref  |
+------+------------+------+
|  300 | 2010-12-12 |    3 |
+------+------------+------+
1 row in set (0.00 sec)

mysql> select * from b  having ref > (select avg(ref) from b);
+------+------------+------+
| id   | d2         | ref  |
+------+------------+------+
|  300 | 2010-12-12 |    3 |
|  300 | 2011-12-12 |    2 |
+------+------------+------+
2 rows in set (0.02 sec)

mysql> select * from b  where ref > (select avg(ref) from b);
+------+------------+------+
| id   | d2         | ref  |
+------+------------+------+
|  300 | 2010-12-12 |    3 |
|  300 | 2011-12-12 |    2 |
+------+------------+------+
2 rows in set (0.00 sec)

mysql> select *,avg(ref) from b  having ref > avg(ref);
+------+------------+------+----------+
| id   | d2         | ref  | avg(ref) |
+------+------------+------+----------+
|  300 | 2010-12-12 |    3 |   1.7500 |
+------+------------+------+----------+
1 row in set (0.00 sec)

Solution 3:[3]

If windowed aggregate functions are supported:

SELECT Salary
FROM (
  SELECT
    Salary,
    AVG(Salary) OVER () AS AvgSalary
  FROM atable
) s
WHERE Salary > AvgSalary

Solution 4:[4]

select empno,e.deptno,sal 
  from emp e, ( select deptno,avg(sal) avsal 
                  from emp 
              group by deptno
              ) a 
 where e.sal > a.avsal 
   and e.deptno = a.deptno;

Solution 5:[5]

its really easy just use following short command given below

SELECT *FROM table_name WHERE salary > avg(select salary from table_name)

HOPE YOU GET IT :-)

Solution 6:[6]

If the name of table is Employee(id, name, city, salary)

select salary from Employee where salary > (select ava(salary) from employee)

Solution 7:[7]

Assuming emp is the name of the table, which has department id as dept_id

  1. Query results shows all employees details whose salary is greater than the average salary of that department. (Department Wise)

(Group by department)

select e1.* from emp e1  inner join (select avg(sal) avg_sal,dept_id from emp group by
dept_id) as e2 on e1.dept_id=e2.dept_id and e1.sal>e2.avg_sal

  1. Query results shows all employees details whose salary is greater than average salary.

    select * from emp where sal > (select avg(sal) from emp)

Solution 8:[8]

Following shall work for you.

SELECT salary FROM table_name WHERE salary > (SELECT AVG(salary) FROM table_name);

Solution 9:[9]

select e.employee_id, e.department_id, e.salary from employees e
where salary > (
select  avg(salary)
from employees d where e.department_id =d.department_id)

Solution 10:[10]

SELECT * FROM table_name WHERE salary > (SELECT AVG(SALARY) from table_name);

Related Questions