HAVING

The HAVING clause filters results obtained by the GROUP BY clause. In the following example, the clause HAVING COUNT(*) 4 eliminates all departments with four or fewer members from the final result. It is similar to the example shown in GROUP BY.

This query:

SELECT DEPT, AVG(SALARY)
FROM Q.STAFF
GROUP BY DEPT
HAVING COUNT(*)  4

Produces this report:

 DEPT      AVG(SALARY)
------ ---------------
   38  15457.110000000
   51  17218.160000000
   66  17215.240000000

Both WHERE and HAVING eliminate data from your report. The WHERE condition is used with column selection. It determines whether an individual row is included. The HAVING condition is used with built-in functions. It determines whether an entire group is included.

HAVING is always followed by a column function (such as SUM, AVG, MAX, MIN, or COUNT). HAVING can also be followed by a subquery that finds a grouped value to complete the HAVING condition. Use WHERE to eliminate unwanted row data and HAVING to eliminate unwanted grouped data.

For example:

• This is correct: HAVING MIN(YEARS) 6
• This is incorrect: HAVING YEARS 6

Example 1

List the smallest, largest, and average salary in each department, excluding managers, for departments with an average salary greater than $12,000:

SELECT DEPT, MIN(SALARY), MAX(SALARY), AVG(SALARY)
FROM Q.STAFF
WHERE JOB < > 'MGR'
GROUP BY DEPT
HAVING AVG(SALARY)  12000

Produces this report:

  DEPT  MIN(SALARY)  MAX(SALARY)         AVG(SALARY)
------  -----------  -----------  ------------------
    15     12258.50     16502.83    13756.5100000000
    20     13504.60     18171.25    15309.5333333333
    38     12009.75     18006.00    14944.7000000000
    42     10505.90     18001.75    13338.7500000000
    51     13369.80     19456.50    16235.2000000000
    66     10988.00     21000.00    16880.1750000000
    84     13030.50     17844.00    15443.0000000000

Remember that HAVING can only be used with grouped data. When the HAVING statement and the GROUP BY statement are both used, the HAVING statement must follow the GROUP BY statement.

Example 2

List, for each number of years of service, the number of employees with that number of years and their average salaries, but only for groups with more than two employees:

SELECT YEARS, COUNT(*), AVG(SALARY)
FROM Q.STAFF
GROUP BY YEARS
HAVING COUNT(*)  2

Produces this report:

 YEARS  COUNT(EXPRESSION 1)         AVG(SALARY)
------  -------------------  ------------------
     5                    5    15552.0400000000
     6                    6    16930.0250000000
     7                    6    18611.8050000000
    10                    3    20162.6000000000
     -                    4    13694.0625000000