Summarizing Information › Specifying a Condition For a Group

Specifying a Condition For a Group

Use HAVING

To apply selection criteria to the result of a GROUP BY clause, use a HAVING clause after the GROUP BY clause. The HAVING clause is similar to a WHERE clause, except that it applies to summary rows only. As in the WHERE clause, you can connect multiple conditional expressions by using AND and OR.

Example

List the departments where the total salary exceeds $300,000:

select deptid, sum(salary) as &xq.total salary',
    avg(salary) as &xq.average salary'
  from emp
  group by deptid
  having sum(salary) > 300000 ! display

                                   EMP REPORT
                                    dd/mm/yy

          DEPTID             TOTAL SALARY              AVERAGE SALARY
          ──────         ─────────────────────         ──────────────
            3100                     348000.00               38666.66
            6666                     496000.00              124000.00
     END OF REPORT