Why Does My Oracle GROUP BY Query Return a 'not a GROUP BY expression' Error?

GROUP BY Without Aggregate Functions

When using GROUP BY without aggregate functions, it's essential to understand how Oracle operates to avoid encountering the "not a GROUP BY expression" error.

GROUP BY groups rows with identical values for the specified columns and produces a single result row. However, columns that don't appear in an aggregate function must be included in the GROUP BY clause to identify the unique combination of grouping values.

Consider the following table:

EMP
+-------+------+------+
| EmpNo | EName | Sal  |
+-------+------+------+
| 7839   | King  | 5000  |
| 7698   | Blake | 2850  |
| 7782   | Clark | 2450  |
+-------+------+------+

Example 1:

SELECT EName, Sal
FROM EMP
GROUP BY EName, Sal

Result:

+------+------+
| EName | Sal  |
+------+------+
| King  | 5000  |
| Blake | 2850  |
| Clark | 2450  |
+------+------+

This query correctly groups the rows and returns all the rows since the grouping columns (EName and Sal) match.

Example 2:

SELECT EName, Sal
FROM EMP
GROUP BY EName

Result:

ORA-00979: not a GROUP BY expression

This query fails because Sal is not included in the GROUP BY clause. Oracle doesn't know how to aggregate the multiple rows with different Sal values for the same EName.

Example 3:

SELECT EName, Sal
FROM EMP
GROUP BY Sal

Result:

ORA-00979: not a GROUP BY expression

This query also fails because EName is not included in the GROUP BY clause. Oracle needs to know which EName to associate with each unique Sal value.

Example 4:

SELECT EmpNo, EName, Sal
FROM EMP
GROUP BY Sal, EName

Result:

ORA-00979: not a GROUP BY expression

This query fails because the number of select columns (EmpNo, EName, Sal) exceeds the number of columns in the GROUP BY clause (Sal, EName). Oracle requires the selected columns to be part of the grouping, considering the unique combinations.

Example 5:

SELECT EmpNo, EName, Sal
FROM EMP
GROUP BY EmpNo, EName, Sal

Result:

+-------+------+------+
| EmpNo | EName | Sal  |
+-------+------+------+
| 7839   | King  | 5000  |
| 7698   | Blake | 2850  |
| 7782   | Clark | 2450  |
+-------+------+------+

This query succeeds because all the selected columns are included in the GROUP BY clause. Oracle considers each unique combination of EmpNo, EName, and Sal, resulting in three distinct rows.

The above is the detailed content of Why Does My Oracle GROUP BY Query Return a 'not a GROUP BY expression' Error?. For more information, please follow other related articles on the PHP Chinese website!