HAVING Example

Restriction: This topic applies to Windows environments only.

The following sample query finds the average quantity of parts supplied for any part distributed by more than one supplier:

SELECT pno, AVG(qty)
FROM partsupp
  GROUP BY pno
  HAVING COUNT(*) >= 2

The query results appear as follows:

pno	AVG(qty)
P1	300
P2	250
P4	250
P5	250

The aggregate property evaluated in a HAVING clause may be an expression involving another aggregate function. Aggregate functions in HAVING clauses are demonstrated in the following query:

SELECT pno, AVG(qty)
FROM partsupp
  GROUP BY pno
  HAVING AVG(qty) * 2 > COUNT(*) * 200

The test condition in a HAVING clause may also include nested queries, as illustrated in the following query:

SELECT pno, AVG(qty)
FROM partsupp
GROUP BY pno
  HAVING AVG(qty) * 2 >
    (SELECT qty
    FROM partsupp
    WHERE pno = "P2" 
      AND sno = "S2")