The Oracle PL/SQL GROUP BY clause enables establishing data groups based on columns. The grouping criterion is defined by the GROUP BY clause, which follows the WHERE clause in the SQL clause hierarchy. Following this hierarchy, result set rows are grouped based on like values of grouping columns and the WHERE clause restricts the entries in each group.
<COLUMN LIST>, <GROUP BY FUNCTIONS>
WHERE <FILTER CONDITIONS>
GROUP BY <COLUMN_LIST>
HAVING <CONDITION BASED ON GROUP BY FUNCTIONS>
- All the columns used besides the aggregate functions must be included in the GROUP BY clause.
- The GROUP BY clause does not support the use of column aliases- you must use the actual column names.
- The GROUP BY columns may or may not appear in the SELECT list.
- The GROUP BY clause can only be used with aggregate functions like SUM, AVG, COUNT, MAX, and MIN. If it is used with single row functions, the Oracle error message appears as "ORA-00979: not a GROUP BY expression".
The SQL query below shows the sum of SALARY within the group formed by a department and job ID (i.e. for respective job IDs in each department).
SELECT DEPTNO, JOB_ID, SUM (SALARY)
GROUP BY DEPTNO JOB_ID
Related Code Snippets: