First Guide to SQL Anywhere Studio
   PART 3. Basic SQL
     CHAPTER 14. Obtaining Aggregate Data       

Using aggregate functions to obtain grouped 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:

1. 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 GROUP BY 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 following error:

Function or column reference to 'emp_lname' in the select list must also appear in a GROUP BY

SQL does not realize that each of the result 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.