Re: select bla, AVG(asd) -- problem

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)