Feb. 4, 2011, 3 a.m.
posted by blackhat
The GROUP BY clause can explicitly appear in a SELECT statement, as in this example:
SELECT column1 FROM Table1 GROUP BY column1
Grouping also happens implicitly if there is a HAVING clause or a set function, as in this example:
SELECT COUNT(*) FROM Table1 HAVING COUNT(*) = 5
In standard SQL—and with InterBase and Microsoft—a GROUP BY column may be followed by a COLLATE clause:
SELECT column1 FROM Table1 GROUP BY column1, column2 COLLATE SQL_Latin1_General
In a nonstandard SQL extension supported by Ingres, Microsoft, MySQL, Oracle, and Sybase, a GROUP BY column may contain an expression:
SELECT LOWER(column1) FROM Table1 GROUP BY LOWER(column1)
And in ANSI SQL:1999, IBM, Microsoft, and Oracle, a GROUP BY clause may contain CUBE or ROLLUP to indicate another level of grouping, like this:
SELECT column1, column2 FROM Table1 GROUP BY CUBE (column1, column2)
The SQL Standard says that the correct name for an aggregate function is "set function," and the required set functions are AVG, COUNT, MAX, MIN, and SUM. In contrast, DBMS vendors prefer the term "aggregate function," and some provide extra built-in functions—for example, for standard deviation (STDEV) and/or variance (VAR)—in addition to the standard set functions. Some DBMSs also allow users to create their own aggregate functions. For example:
SELECT AVG(column1), STDEV(column1), UDF1(column1) FROM Table1 WHERE column1 > 55 GROUP BY column1 ORDER BY column1
Most of the features we've mentioned are supported by most DBMSs. Figure shows the SQL Standard requirements and the level of support the Big Eight have for GROUP BY.
Notes on Figure: