Grouping Rows with GROUP BY
Go Up to Understanding Data Retrieval with SELECT
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;