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, execute the following query:

SELECT SalesRepresentative, COUNT( * )
   FROM SalesOrders
   GROUP BY SalesRepresentative
   ORDER BY SalesRepresentative;

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

A GROUP BY clause tells SQL 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 SalesRepresentative value.

Aggregate functions such as COUNT are applied to the rows in each group. So, 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. See The GROUP BY clause: Organizing query results into groups.

Semantic differences with the empty set

The SQL language treats the empty set differently when using aggregate functions. Without a GROUP BY clause, a query containing an aggregate function over zero input rows will return a single row as the result. In the case of COUNT, its result will be the value zero, and with all other aggregate functions the result will be NULL. However, if the query contains a GROUP BY clause, and the input to the query is empty, then the query's result is empty and no rows are returned.

For example, the following query returns a single row with the value 0; there are no employees in department 103.

SELECT COUNT() FROM Employees WHERE DepartmentID = 103;

However, this modified query returns no rows, due to the presence of the GROUP BY clause.

SELECT COUNT() FROM Employees WHERE DepartmentID = 103 GROUP BY State;

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, the following query gives an error.

SELECT SalesRepresentative, Surname, COUNT( * )
   FROM SalesOrders KEY JOIN Employees
   GROUP BY SalesRepresentative;

The error message indicates that a reference to the Surname column must also appear in the GROUP BY clause. This error occurs because SQL Anywhere cannot verify that each of the result rows for an employee with a given ID have the same last name.

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

SELECT SalesRepresentative, Surname, COUNT( * )
   FROM SalesOrders KEY JOIN Employees
   GROUP BY SalesRepresentative, Surname
   ORDER BY SalesRepresentative;

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

SELECT SalesRepresentative, MAX( Surname ), COUNT( * )
   FROM SalesOrders KEY JOIN Employees
   GROUP BY SalesRepresentative
   ORDER BY SalesRepresentative;

The MAX function chooses the maximum (last alphabetically) Surname 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 Surname appears on every detail row within a group.

Discuss this page in DocCommentXchange.