SQL Statements › SELECT › Subselect › Example 4

Example 4

Show the department number and maximum departmental salary for all departments whose maximum salary is less than the average salary for all employees. The information is in the employee table TEMPL. In this example, the subselect would be executed only once.

      SELECT WORKDEPT, MAX(SALARY)
      FROM CA.TEMPL
      GROUP BY WORKDEPT
      HAVING MAX(SALARY) < (SELECT AVG(SALARY)
                           FROM CA.TEMPL)