* Edouard Lauer > I would like to query the littlest average salary. I have a table with > employees and their salary like that: > > +-----------+------+ > | job | sal | > +-----------+------+ > | CLERK | 800 | > | SALESMAN | 1600 | > | SALESMAN | 1250 | > | MANAGER | 2975 | > | SALESMAN | 1250 | > | MANAGER | 2850 | > | MANAGER | 2450 | > | ANALYST | 3000 | > | PRESIDENT | 5000 | > | SALESMAN | 1500 | > | CLERK | 1100 | > | CLERK | 950 | > | ANALYST | 3000 | > | CLERK | 1300 | > +-----------+------+ > > Now this query returns the average salary per job: > > select job,avg(sal) from emp group by job --> > > +-----------+-----------+ > | job | avg(sal) | > +-----------+-----------+ > | ANALYST | 3000.0000 | > | CLERK | 1037.5000 | > | MANAGER | 2758.3333 | > | PRESIDENT | 5000.0000 | > | SALESMAN | 1400.0000 | > +-----------+-----------+ > > The final result should be: > > +-----------+-----------+ > | job | avg(sal) | > +-----------+-----------+ > | CLERK | 1037.5000 | > +-----------+-----------+ > > In ORACLE I can do it like this: > select job,avg(sal) from emp group by job having avg(sal)=(select > min(avg(sal)) from emp group by job); > > but this doesn't work in MYSQL. Does somebody know how it can be done in > MySQL???
Yes, you can add an alias, an ORDER BY clause and LIMIT 1 to your query: SELECT job,AVG(sal) AS avg_sal FROM emp GROUP BY job ORDER BY avg_sal LIMIT 1 <URL: http://www.mysql.com/doc/en/SELECT.html > -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
