select bla, AVG(asd) -- problem

From: "alex b(dot)" <keex(at)gmx(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: select bla, AVG(asd) -- problem
Date: 2003-04-02 19:42:26
Message-ID: 3E8B3D22.4030706@gmx.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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)

Browse pgsql-general by date

  From Date Subject
Next Message alex b. 2003-04-02 19:45:03 select bla, AVG(asd) -- problem
Previous Message Jan Wieck 2003-04-02 19:41:12 Re: Trigger functions