ASA Getting Started
  Selecting Aggregate Data

Applying aggregate functions to grouped data

In addition to providing information about an entire table, aggregate functions can be used on groups of rows. The GROUP BY clause arranges rows into groups, and aggregate functions return a single value for each group of rows.

Example 

List the sales representatives and the number of orders each has taken

• In Interactive SQL, type the following in the SQL Statements pane and press F5 to execute the statement.

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

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

A GROUP BY clause tells Adaptive Server Anywhere to partition the set of all the rows that would otherwise be returned. All rows in each partition, or group, have the same values in the named column or columns. There is only one group for each unique value or set of values. In this case, all the rows in each group have the same sales_rep value.

Aggregate functions such as COUNT are applied to the rows in each group. Thus, this result set displays the total number of rows in each group. The results of the query consist of one row for each sales rep ID number. Each row contains the sales rep ID, and the total number of sales orders for that sales representative.

Whenever GROUP BY is used, the resulting table has one row for each column or set of columns named in the GROUP BY clause.

For more information, see The GROUP BY clause: organizing query results into groups.

A common error with GROUP BY 

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

-- This query is incorrect
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

An error is reported because Adaptive Server Anywhere cannot be sure that each of the result rows for an employee with a given ID all have the same last name.

To fix this error, add the column to the GROUP BY clause.

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

If this is not appropriate, you can instead use an aggregate function to select only one value, as shown:

SELECT sales_rep, MAX( emp_lname ), COUNT( * )
FROM sales_order KEY JOIN employee
GROUP BY sales_rep
ORDER BY sales_rep

The MAX function chooses the maximum (last alphabetically) last name from the detail rows for each group. This statement is valid because there can be only one distinct maximum value. In this case, the same last name appears on every detail row within a group.