You can nest SELECT statements in the WHERE or HAVING clause of other SELECT statements.
In this example, you will use the SQL Editor to create the following query:
SELECT STAFF.NAME, STAFF.JOB, STAFF.DEPT, STAFF.SALARY FROM STAFF GROUP BY STAFF.DEPT, STAFF.JOB, STAFF.SALARY, STAFF.NAME HAVING (STAFF.SALARY > (SELECT AVG(STAFF.SALARY) FROM STAFF))
Using the GROUP BY Details window
Now you are ready to specify the grouping of the result table rows using a GROUP BY clause.
This displays the GROUP BY Details window, as shown in the following
illustration. Use this window to specify how you want to group the rows
of the result table.
![]() | If you need to change the grouping precedence, use the Move after and Move before options on the GROUP BY sequence list's pop-up menu. |
In this example, the rows that are returned in the result table are grouped first by department, meaning that all rows with the same department number are shown together. Within each department number group, the rows are further grouped by job type, then by salary, then by name.
Using the HAVING Details window
Now you are ready to specify the nested SELECT statement using a HAVING clause.
This displays the HAVING Details window, as shown in the following
illustration. Use this window to specify how you want to restrict the
groups of rows of the intermediate result table.
SELECT AVG(STAFF.SALARY) FROM STAFF
In the next section you will learn how to add a computed column to this query.
Because the column in the SELECT clause is a computed column, you must first create it.
Any computed columns you create are displayed in the Computed columns list and can be included in the Column sequence list or the SELECT clause of your query.
Also notice that you can edit and delete existing computed columns using the pop-up menu on the Computed columns list.
When you return to the Select a query window, your newly created query is selected.
In this sample statement, the groups of rows in the result table are restricted to those with a salary greater than the average salary of all STAFF table entries.
![]() | If you are using DB2 V2.1, ODBC, or ORACLE support, then the Computed Column Details window can have one extra field. Some databases support named computed columns. If you enter a name for a computed column, then the computed column appears on the Computed columns field under this name. Computed column names cannot have spaces in them. |