Page 73
included in the computation. When an asterisk (*) is placed within the function, all the records are counted. Listing 3.44 shows what happens when the * is used.
Listing 3.44L_03_44.TXTExample of the Count Function
SQL> select count(*), count(wages) from employee; COUNT(*) COUNT(WAGES) -------- --------------------- 19 17
The first column in the select statement counts each row selected. The second column counts only those records that contain a value in the wages column. When the word distinct is placed within the function, the function counts records of the same value only once. It computes the number of different values in the set. In the example employee table, three employees are named `ROOSEVELT'. Listing 3.45 uses the distinct word; notice how many times this value was counted.
Listing 3.45L_03_45.TXTExample of Using the Count and Distinct Functions
SQL> select count(distinct last_name) 2 from employee 3 where last_name = `ROOSEVELT'; COUNT(DISTINCTLAST_NAME) 1
It is possible to use more than one group function in the same select clause. Listing 3.46 computes the last names with the minimum value and maximum value, the number of records in the table, and the number of distinct last names.
Listing 3.46L_03_46.TXTExample of Multiple Group Functions
SQL> select min(last_name), max(last_name), 2 count(*), count(last_name) 3 from employee; MIN(LAST_NAME) MAX(LAST_NAME) COUNT(*) COUNT(LAST_NAME) -------------- -------------- -------- ---------------- ANTHONY WILSON 19 19
The group functions discussed so far have been performed on sets of data that consist of the entire table. It is possible to perform computations on multiple subsets of data. Listing the
Page 74
subset column names in the group by clause causes Oracle7 to group the selected records in subsets. The group by function is them performed against the subset. Listing 3.47 includes an example that performs computations on subsets of the entire data set. This example computes the total amount of wages per last_name.
Listing 3.47L_03_47.TXTExample of the Group By Clause
SQL> select fk_department, sum(wages) 2 from employee 3 group by fk_department; FK_D SUM(WAGES) ---- ---------- INT 55500 POL 77900 WEL 52000
You do not have to list the columns listed in the group by clause in the select clause. Of course when they are not listed, it is difficult to determine what set of columns the function has computed a value for.
The columns do not have to be in any particular order in the select clause (see Listing 3.48). The order that the columns and functions are listed in the select clause should be determined by the desired presentation style.
If you list a column in the select clause and do not list it in the group by clause, however, an error occurs. The error is the result of the statement having ambiguity. Oracle7 does not know the columns that determine the subsets of data. The example shown in Listing 3.48 contains an additional column not included in the group by clause.
Listing 3.48L_03_48.TXTExample of Column Ambiguity Error
SQL> select last_name, first_name, sum(wages) 2 from employee; select last_name, first_name, sum(wages) * ERROR at line 1: ORA-00937: not a single-group group function
This statement resulted in an error because the select clause had a group function preceded by two columns, and the statement did not have a group by clause. Oracle7 doesn't know how to calculate the function. When a group by clause is included in the SELECT statement, the error is eliminated (see Listing 3.49).
Listing 3.49L_03_49.TXTExample of Mixing Function and Group Columns
SQL> select sum(wages), last_name, first_name 2 from employee 3 group by last_name, first_name;
Page 75
SUM(WAGES) LAST_NAME FIRST_NAME ---------- --------------- --------------- 7000 ANTHONY SUSANNE 14000 BUSH GEORGE 13000 CARTER JIMMY 15000 CLINTON WILLIAM 9500 COOLIDGE CALVIN 0 EISENHOWER DWIGHT 13000 FORD GERALD 10000 HOOVER HERBERT 7500 JOHNSON ANDREW
You cannot include group functions in the where clause even though it is desirable to limit the number of records based on the value of the function. Assume, for example, that you want to produce a listing that contains only departments that have total wages greater $20,000. The statement computing this listing needs the group function sum in the where clause to limit the records. You cannot place this function in that clause, but you can place it in the having clause. This clause is used to limit the number of records selected (see Listing 3.50) through the use of a group function.
Listing 3.50L_03_50.TXTExample of the Having Clause
SQL> select fk_department, sum(wages) 2 from employee 3 group by fk_department 4 having sum(wages) > 20000; FK_D SUM(WAGES) ---- ---------- INT 55500 POL 77900 WEL 52000 3 records selected.
The preceding example selects the departments that have total wages greater than $20,000. The having clause follows the group by clause in the select statement.
Subqueries are used in the where clause to create a virtual list of one or more values used in the conditional statement for evaluations. The subquery is performed before the main query because it is nested. After the subquery is completed and the values are determined, the main query is executed.
Page 76
The benefit of the subquery is that values are determined at the time the select is performed. This means that values do not have to be hard coded. Hard-coded values may become obsolete over time. The subquery reduces the need to rewrite the query. A second benefit is that the subquery avoids the necessity of running one query, obtaining the result, and hard coding this value in the where clause of the second query. Listing 3.51 shows this benefit. The example query determines the name of the oldest employee in the employee database. Without the subquery, you must perform two queries. The first determines the oldest birthdate in the table. The second determines the name of the employee with that birthdate. The use of the subquery reduces this to one query.
Listing 3.51L_03_51.TXTExample of the Use of a Subquery
SQL> select last_name, first_name, birth_date 2 from employee 3 where birth_date = (select min(birth_date) from employee); LAST_NAME FIRST_NAME BIRTH_DAT --------------- --------------- --------- ANTHONY SUSAN 15-FEB-20 1 record selected.
The preceding example produces one value in the subquery. If the subquery produces multiple values, the IN function should be used. A where clause can contain the amount of subqueries as needed. Listing 3.52 shows an example of a multiple subselect query.
Listing 3.52 L_03_52.TXTExample of Multiple Subqueries
SQL> select last_name, first_name, fk_department 2 from employee 3 where fk_department = (select fk_department from employee 4 where last_name = `ROOSEVELT' 5 and first_name = `FRANKLIN') 6 and employment_date > (select employment_date from employee 7 where last_name = `ROOSEVELT' 8 and first_name = `FRANKLIN'); LAST_NAME FIRST_NAME FK_D --------------- --------------- ---- JOHNSON LYNDON POL CLINTON WILLIAM POL NIXON RICHARD POL KENNEDY JOHN POL 4 records selected.
This query performs multiple subqueries to determine FRANKLIN ROOSEVELT's department and hire date. It then selects employees in the department that were hired before him.