Using the GROUP BY and HAVING Clauses
The optional GROUP BY and HAVING clauses add functionality to your SELECT statement. You can include one or both in a basic SELECT statement to increase your ability to manipulate aggregates.
The GROUP BY clause combines similar rows, producing a single result row for each group of rows that have the same values for each column listed in the select list. The HAVING clause sets conditions on those groups after you form them. You can use a GROUP BY clause without a HAVING clause, or a HAVING clause without a GROUP BY clause.
Using the GROUP BY Clause
The GROUP BY clause divides a table into sets. This clause is most often combined with aggregate functions that produce summary values for each of those sets. Some examples in Chapter 2, "Composing Simple SELECT Statements" show the use of aggregate functions applied to a whole table. This chapter illustrates aggregate functions applied to groups of rows.
Using the GROUP BY clause without aggregates is much like using the DISTINCT (or UNIQUE) keyword in the SELECT clause. Chapter 2, "Composing Simple SELECT Statements," included the statement found in Query 3-1a.
Query 3-1a
You can also write the statement as Query 3-1b shows.
Query 3-1b
Query 3-1a and Query 3-1b return the rows that Query Result 3-1 shows.
The GROUP BY clause collects the rows into sets so that each row in each set has equal customer numbers. With no other columns selected, the result is a list of the unique customer_num values.
The power of the GROUP BY clause is more apparent when you use it with aggregate functions.
Query 3-2 retrieves the number of items and the total price of all items for each order.
Query 3-2
The GROUP BY clause causes the rows of the items table to be collected into groups, each group composed of rows that have identical order_num values (that is, the items of each order are grouped together). After you form the groups, the aggregate functions COUNT and SUM are applied within each group.
Query 3-2 returns one row for each group. It uses labels to give names to the results of the COUNT and SUM expressions, as Query Result 3-2 shows.
Query Result 3-2 collects the rows of the items table into groups that have identical order numbers and computes the COUNT of rows in each group and the sum of the prices.
You cannot include a column having a CLOB, BLOB, TEXT, or BYTE data type in a GROUP BY clause. To group, you must be able to sort, and no natural sort order exists for CLOB, BLOB, TEXT, or BYTE data.
Unlike the ORDER BY clause, the GROUP BY clause does not order data. Include an ORDER BY clause after your GROUP BY clause if you want to sort data in a particular order or sort on an aggregate in the select list.
Query 3-3 is the same as Query 3-2 but includes an ORDER BY clause to sort the retrieved rows in ascending order of price, as Query Result 3-3 shows.
Query 3-3
As stated in Chapter 2, "Composing Simple SELECT Statements," you can use an integer in an ORDER BY clause to indicate the position of a column in the select list. You also can use an integer in a GROUP BY clause to indicate the position of column names or display labels in the group list.
Query 3-4 returns the same rows as Query 3-3, as Query Result 3-3 shows.
Query 3-4
When you build a query, remember that all nonaggregate columns that are in the select list in the SELECT clause must also be included in the group list in the GROUP BY clause. The reason is that a SELECT statement with a GROUP BY clause must return only one row per group. Columns that are listed after GROUP BY are certain to reflect only one distinct value within a group, and that value can be returned. However, a column not listed after GROUP BY might contain different values in the rows that are contained in a group.
As Query 3-5 shows, you can use the GROUP BY clause in a SELECT statement that joins tables.
Query 3-5
Query 3-5 joins the orders and items tables, assigns table aliases to them, and returns the rows that Query Result 3-5 shows.
Using the HAVING Clause
The HAVING clause usually complements a GROUP BY clause by applying one or more qualifying conditions to groups after they are formed, which is similar to the way the WHERE clause qualifies individual rows. One advantage to 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.
Query 3-6 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 two or more rows.
Query 3-6
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.
Query 3-7, a modified version of Query 3-6, returns just one row, the average of all total_price values in the table.
Query 3-7
If Query 3-7, like Query 3-6, had included the nonaggregate column order_num in the select list, 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.
Query 3-8 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).
Query 3-8
Query 3-8 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 following results in a temporary table. If you query with SELECT * from that table, you see the rows as Query Result 3-8 shows.
|
