Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Grouping and Summarizing

SQL allows you to divide the rows returned by a query into groups, summarize the data within each group using a special class of functions known as aggregate functions , and return only one row per group. For example, you can count the number of rows in a table using the COUNT function shown in Example 4-28.

Example 4-28. Summarizing data using an aggregate function

               SELECT COUNT(*), COUNT(employee_termination_date)
               FROM employee;

  COUNT(*) COUNT(EMPLOYEE_TERMINATION_DATE)
---------- --------------------------------
        11                                6

There's nothing in Example 4-28 to divide the data being retrieved into groups, so all 11 rows in the employee table are treated as one group. The COUNT function is an aggregate function that can count the number of values or rows in a group. COUNT is special in that you can pass it an asterisk (*) when you wish to count rows. The first use of COUNT in the example shows that the table contains 11 rows. The second use counts the number of values in the employee_termination_date column. Nulls are not counted because nulls represent the absence of value. While there are 11 employees on file, only five are currently employed; the other six have been terminated. This is the kind of business information you can obtain by summarizing your data.

The GROUP BY Clause

You'll rarely want to summarize data across an entire table. More often, you'll find yourself dividing your data into groups. For example, you might wish to group employees by the decade in ...