Standards and compatibility

User's Guide
PART 1. Working with Databases
CHAPTER 5. Summarizing, Grouping, and Sorting Query Results

Standards and compatibility

This section describes standards and compatibility aspects of the Adaptive Server Anywhere GROUP BY clause.

GROUP BY and the SQL/92 standard

The SQL/92 standard for GROUP BY requires the following:

The columns in a select list must be in the GROUP BY expression or they must be arguments of aggregate functions.
A GROUP BY expression can only contain column names from the select list, but not those used only as arguments for vector aggregates.

The results of a standard GROUP BY with vector aggregate functions produce one row and one summary value per group.

Adaptive Server Anywhere and Adaptive Server Enterprise support extensions to HAVING that allow aggregate functions not in the select list and not in the GROUP BY clause.

Compatibility with Adaptive Server Enterprise

Adaptive Server Enterprise supports several extensions to the GROUP BY clause that are not supported in Adaptive Server Anywhere. These include the following:

Non-grouped columns in the select list Adaptive Server Enterprise permits column names in the select list that do not appear in the group by clause. For example, the following is valid in Adaptive Server Enterprise:
```
SELECT name, unit_price
FROM product
GROUP BY name
```
This syntax is not supported in Adaptive Server Anywhere.
Nested aggregate functions The following query, which nests a vector aggregate inside a scalar aggregate, is valid in Adaptive Server Enterprise but not in Adaptive Server Anywhere:
```
SELECT MAX(AVG(unit_price))
FROM product
GROUP BY name
```
GROUP BY and ALL Adaptive Server Anywhere does not support the use of ALL in the GROUP BY clause.
HAVING with no GROUP BY Adaptive Server Anywhere does not support the use of HAVING with no GROUP BY clause
HAVING conditions Adaptive Server Enterprise supports extensions to HAVING that allow non-aggregate functions not in the select list and not in the GROUP BY clause. Only aggregate functions of this type are allowed in Adaptive Server Anywhere.
DISTINCT with ORDER BY or GROUP BY Adaptive Server Enterprise permits the use of columns in the ORDER BY or GROUP BY clause that do not appear in the select list, even in SELECT DISTINCT queries. This can lead to repeated values in the SELECT DISTINCT result set. Adaptive Server Anywhere does not support this behavior.
Column names in UNIONS Adaptive Server Enterprise permits the use of columns in the ORDER BY clause in unions of queries. In Adaptive Server Anywhere, the ORDER BY clause must use an integer to mark the column being ordered.