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.

The following query 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 1. Query

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

Figure 2. 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.

The following query, a modified version of Query, returns just one row, the average of all total_price values in the table, as the result shows.

Figure 3. Query

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

Figure 4. Query result

         average

         $270.97

If Query, like Query, had included the non-aggregate 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.

The following query 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 5. 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;

The query 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 the result shows.

Figure 6. 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