How to determine salaries greater than the average salary

Question

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

Answer 1

Try something like this:

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

Answer 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)