Using the HAVING Clause

To complement a GROUP BY clause, use a HAVING clause to apply one or more qualifying conditions to groups after they are formed. The effect of the HAVING clause on groups is similar to the way the WHERE clause qualifies individual rows. One advantage of using a HAVING clause is that you can include aggregates in the search condition, whereas you cannot include aggregates in the search condition of a WHERE clause.

Each HAVING condition compares one column or aggregate expression of the group with another aggregate expression of the group or with a constant. You can use HAVING to place conditions on both column values and aggregate values in the group list.

Figure 272 returns the average total price per item on all orders that have more than two items. The HAVING clause tests each group as it is formed and selects those that are composed of more than two rows.

Figure 272. Query

SELECT order_num, COUNT(*) number, AVG (total_price) average
   FROM items
   GROUP BY order_num
   HAVING COUNT(*) > 2

Figure 273. Query Result

  order_num      number          average

       1003           3          $319.67
       1004           4          $354.00
       1005           4          $140.50
       1006           5           $89.60
       1007           5          $339.20
       1013           4           $35.95
       1016           4          $163.50
       1017           3          $194.67
       1018           5          $226.20
       1021           4          $403.50
       1022           3           $77.33
       1023           6          $137.33

If you use a HAVING clause without a GROUP BY clause, the HAVING condition applies to all rows that satisfy the search condition. In other words, all rows that satisfy the search condition make up a single group.

Figure 274, a modified version of Figure 272, returns just one row, the average of all total_price values in the table, as Figure 275 shows.

Figure 274. Query

SELECT AVG (total_price) average
   FROM items
   HAVING count(*) > 2

Figure 275. Query Result

         average

         $270.97

If Figure 274, like Figure 272, had included the nonaggregate column order_ num in the projection clause, you would have to include a GROUP BY clause with that column in the group list. In addition, if the condition in the HAVING clause was not satisfied, the output would show the column heading and a message would indicate that no rows were found.

Figure 276 contains all the SELECT statement clauses that you can use in the Informix version of interactive SQL (the INTO clause that names host variables is available only in an SQL API).

Figure 276. Query

SELECT o.order_num, SUM (i.total_price) price,
     paid_date - order_date span
   FROM orders o, items i
   WHERE o.order_date > '01/01/98'
      AND o.customer_num > 110
      AND o.order_num = i.order_num
   GROUP BY 1, 3
   HAVING COUNT (*) < 5
   ORDER BY 3
   INTO TEMP temptab1

Figure 276 joins the orders and items tables; employs display labels, table aliases, and integers that are used as column indicators; groups and orders the data; and puts the results in a temporary table, as Figure 277 shows.

Figure 277. Query Result

  order_num            price        span

       1017          $584.00            
       1016          $654.00            
       1012         $1040.00            
       1019         $1499.97          26
       1005          $562.00          28
       1021         $1614.00          30
       1022          $232.00          40
       1010           $84.00          66
       1009          $450.00          68
       1020          $438.00          71