User's Guide

Part II. Tutorials

Chapter 9. Obtaining Aggregate Data

In addition to providing information about an entire table, aggregate functions can be used on groups of rows.

To list the number of orders each sales representative is responsible for:

• Type the following:

       SELECT sales_rep, count( * )
       FROM sales_order
       GROUP BY sales_rep
  

sales_rep	count( * )
129	57
195	50
299	114
467	56
667	54

The results of this query consist of one row for each sales rep ID number, containing the sales rep ID, and the number of rows in the sales_order table with that number.

Whenever GROUP BY is used, the resulting table has one row for each different value found in the GROUP BY column or columns.

A common error with GROUP BY

A common error with groups is to try to get information which cannot properly be put in a group. For example,

     SELECT sales_rep, emp_lname, count( * )
     FROM sales_order
     KEY JOIN employee
     GROUP BY sales_rep

gives the error

     column 'emp_lname' cannot be used unless it is in a GROUP BY.

SQL does not realize that each of the rows for an employee with a given ID have the same value of emp_lname. An error is reported since SQL does not know which of the names to display.

However, the following is valid:

     SELECT sales_rep, max( emp_lname ), count( * )
     FROM sales_order
     KEY JOIN employee
     GROUP BY sales_rep

The max function chooses the maximum (last alphabetically) surname from the detail rows for each group. The surname is the same on every detail row within a group so the max is just a trick to bypass a limitation of SQL.