select bla, AVG(asd) -- problem - Mailing list pgsql-general

I'm using:
  PostgreSQL 7.3.2 on i486-pc-none, compiled by GCC egcs-2.91.66


I want to print out all employees, who earn more than the average,
unfortunately M$-ACCESS does this just right, unlike PostgreSQL.. :(

but it is very possible that I am wrong myself.. :)

what am I doing wrong here?


SELECT ename, sal, AVG(sal)
FROM emp WHERE sal > (SELECT AVG(sal) FROM emp)
GROUP BY ename, sal;

  ename | sal  |        avg
-------+------+--------------------
  BLAKE | 2850 | 2850.0000000000000
  CLARK | 2450 | 2450.0000000000000
  FORD  | 3000 | 3000.0000000000000
  JONES | 2975 | 2975.0000000000000
  KING  | 5000 | 5000.0000000000000
  SCOTT | 3000 | 3000.0000000000000
(6 rows)



SELECT ename, sal, (SELECT AVG(sal) FROM emp)
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp)
GROUP BY ename, sal;

  ename | sal  |      ?column?
-------+------+--------------------
  BLAKE | 2850 | 2055.3571428571429
  CLARK | 2450 | 2055.3571428571429
  FORD  | 3000 | 2055.3571428571429
  JONES | 2975 | 2055.3571428571429
  KING  | 5000 | 2055.3571428571429
  SCOTT | 3000 | 2055.3571428571429
(6 rows)





SELECT *
FROM emp;

  empno | ename  |    job    | mgr  |  hiredate  | sal  | comm | deptno
-------+--------+-----------+------+------------+------+------+--------
   7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 |      |     20
   7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30
   7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30
   7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30
   7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100 |      |     20
   7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 |      |     30
   7984 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 |      |     10
   7566 | JONES  | MANAGER   | 7739 | 1981-04-02 | 2975 |      |     20
   7698 | BLAKE  | MANAGER   | 7739 | 1981-05-01 | 2850 |      |     30
   7782 | CLARK  | MANAGER   | 7739 | 1981-06-09 | 2450 |      |     10
   7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000 |      |     20
   7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 |      |     20
   7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000 |      |     10
   7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1250 |    0 |     30
(14 rows)