Re: select bla, AVG(asd) -- problem - Mailing list pgsql-general
From | Stephan Szabo |
---|---|
Subject | Re: select bla, AVG(asd) -- problem |
Date | |
Msg-id | 20030402115543.R65473-100000@megazone23.bigpanda.com Whole thread Raw |
In response to | select bla, AVG(asd) -- problem ("alex b." <mailinglists1@gmx.de>) |
List | pgsql-general |
On Wed, 2 Apr 2003, alex b. wrote: > 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? Errm, what are you expecting? With the exception that I'm not sure that the subselect inside the group by is necessarily right, the output you're getting from these queries seems right (assuming that 2055 really is the avg). > 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) Here you're asking for the avg(sal) within each group, but each group only has one row, so the avg is equal to the value. > 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) Here you're asking for the avg salary over the entire table. I'm also not sure you actually need/want a group by in this case. > 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)
pgsql-general by date: