HP 3000 Manuals HP 3000 Manuals
The GROUP BY and HAVING Clauses The GROUP BY clause identifies columns used for grouping when an aggregate function is applied to a group of rows. When you use the GROUP BY clause, the select list must contain the columns you intend to group by and the aggregate functions. If your select list includes an asterisk to retrieve all the columns in a table, each column must be specified in the GROUP BY clause. The HAVING clause specifies a condition that each group must satisfy in order for that group to be selected for the query result. The HAVING clause cannot be used without the GROUP BY clause. The HAVING clause must contain an aggregate function or the columns named in the select list. If the WHERE clause is being used, the GROUP BY, HAVING, and ORDER BY clauses must follow the WHERE clause. Otherwise, they follow the FROM clause. ALLBASE/SQL uses the following order to return your query result: * The WHERE clause eliminates rows before groups are formed. * The GROUP BY clause groups the resulting rows. * The HAVING clause eliminates groups. * The select list aggregate functions are computed for each group. Some examples of the GROUP BY and HAVING clauses follow. In the following example, part numbers (which are supplied by various vendors) with a minimum unit price of less than $400.00 are selected. This example shows an aggregate function (MIN) used in both the select list and the HAVING clause. __________________________________________________________________________________ | | | isql=> SELECT PartNumber, MIN (UnitPrice) | | > FROM PurchDB.SupplyPrice | | > GROUP BY PartNumber | | > HAVING MIN (UnitPrice) < 400.00; | | | | SELECT PartNumber, MIN (UnitPrice) FROM PurchDB.SupplyPrice | | GROUP BY PartNu... | | ----------------+------------------ | | PARTNUMBER |(EXPR) | | ----------------+------------------ | | 1133-P-01 | 180.00 | | 1143-P-01 | 175.00 | | 1153-P-01 | 200.00 | | 1223-MU-01 | 75.00 | | 1233-MU-01 | 285.00 | | 1243-MU-01 | 95.00 | | 1323-D-01 | 190.00 | | 1333-D-01 | 195.00 | | 1423-M-01 | 335.00 | | 1523-K-01 | 195.00 | | 1723-AD-01 | 230.00 | | 1733-AD-01 | 225.00 | | 1923-PA-01 | 70.00 | | | | | | | | ---------------------------------------------------------------------------| | Number of rows selected is 13 | | U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] | | <n>, or e[nd] > end | __________________________________________________________________________________ In the next example, part numbers are grouped for each vendor number. Groups of rows for vendors 9006, 9010, and 9015 are not included in the query result. No aggregate function is used. __________________________________________________________________________________ | | | isql=> SELECT VendorNumber, PartNumber | | > FROM PurchDB.SupplyPrice | | > GROUP BY VendorNumber, PartNumber | | > HAVING VendorNumber NOT IN (9006, 9010, 9015); | | | | SELECT VendorNumber, PartNumber FROM PurchDB.SupplyPrice | | GROUP BY VendorNum... | | ------------+---------------- | | VENDORNUMBER|PARTNUMBER | | ------------+---------------- | | 9001|1343-D-01 | | 9001|1933-FD-01 | | 9002|1123-P-01 | | 9002|1133-P-01 | | 9002|1153-P-01 | | 9002|1823-PT-01 | | 9002|1923-PA-01 | | 9003|1123-P-01 | | 9003|1133-P-01 | | 9003|1153-P-01 | | 9003|1433-M-01 | | 9003|1933-FD-01 | | 9004|1143-P-01 | | 9004|1323-D-01 | | 9004|1333-D-01 | | 9004|1723-AD-01 | | ---------------------------------------------------------------------------| | First 16 rows have been selected. | | U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] | | <n>, or e[nd] > end | __________________________________________________________________________________