Menu
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
206
How to display employee name and his salary whose salary is greater than highest average of department number? SELECT ename,deptno,sal FROM emp WHERE sal> (SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno ); Share the knowledge!
Find values that are well above the average The simplest way is to use a fixed margin, for example 1000: SELECT name, surname, salary FROM employee WHERE (salary - 1000) > ( SELECT avg(salary) FROM employee ) ; But maybe you don't want a fixed value, you want something smarter.
To calculate salary average, you'll add up all the salaries in your chosen group and divide by the people in that group.
Try something like this:
SELECT salary WHERE salary > (SELECT AVG(salary) FROM *)
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
