Grouping Rows with GROUP BY
Go Up to Understanding Data Retrieval with SELECT
Contents
The optional GROUP BY
clause enables a query to return summary information about groups of rows that share column values instead of returning each qualifying row. The complete syntax of GROUP BY
is:
GROUP BY col [COLLATE collation] [, col [COLLATE collation] ...]
For example, consider two cursor declarations. The first declaration returns the names of all employees each department, and arranges retrieval in ascending alphabetic order by department and employee name.
EXEC SQL
DECLARE DEPT_EMP CURSOR FOR
SELECT DEPARTMENT, LAST_NAME, FIRST_NAME
FROM DEPARTMENT D, EMPLOYEE E
WHERE D.DEPT_NO = E.DEPT_NO
ORDER BY DEPARTMENT, LAST_NAME, FIRST_NAME;
In contrast, the next cursor illustrates the use of aggregate functions with GROUP BY
to return results known as group aggregates. It returns the average salary of all employees in each department. The GROUP BY
clause assures that average salaries are calculated and retrieved based on department names, while the ORDER BY
clause arranges retrieved rows alphabetically by department name.
EXEC SQL
DECLARE AVG_DEPT_SAL CURSOR FOR
SELECT DEPARTMENT, AVG(SALARY)
FROM DEPARTMENT D, EMPLOYEE E
WHERE D.DEPT_NO = E.DEPT_NO
GROUP BY DEPARTMENT
ORDER BY DEPARTMENT;
Collation Order in a GROUP BY Clause
When CHAR
or VARCHAR
columns are grouped in a SELECT
statement, it can be necessary to specify a collation order for the grouping, especially if columns used for grouping use different collation orders.
To specify the collation order to use for grouping columns in the GROUP BY
clause, include a COLLATE
clause after the column name. For example, in the following GROUP BY
clause, the collation order for two columns is specified:
. . .
GROUP BY LNAME COLLATE FR_CA, FNAME COLLATE FR_CA;
For more information about collation order and a list of collation orders available in InterBase, see the Data Definition Guide.
Limitations of GROUP BY
When using GROUP BY
, be aware of the following limitations:
- Each column name that appears in a
GROUP BY
clause must also be specified in theSELECT
clause. GROUP BY
cannot specify a column whose values are derived from a mathematical, aggregate, or user-defined function.GROUP BY
cannot be used inSELECT
statements that:
- Contain an
INTO
clause (singleton selects). - Use a subquery with a
FROM
clause which references a view whose definition contains aGROUP BY
orHAVING
clause.
- Contain an
- For each
SELECT
clause in a query, including subqueries, there can only be oneGROUP BY
clause.
Enhancements to GROUP BY
InterBase 2017 Update 1 introduces enhancements to the GROUP BY syntax. For more information refer to: Enhancements to GROUP BY and ORDER BY.