Previous | Table of Contents | Next

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.TXT—Example 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.TXT—Example 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

Multiple Group Functions

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.TXT—Example 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

Understanding GROUP BY Functions

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.TXT—Example 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.TXT—Example 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.TXT—Example 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

Understanding the HAVING Clause

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.TXT—Example 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.

Understanding Subselects or Subqueries

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.TXT—Example 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.TXT—Example 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.

Previous | Table of Contents | Next