How Does SQL's GROUP BY Clause Work Without Aggregate Functions?

Understanding GROUP BY Without Aggregate Functions

In SQL, the GROUP BY statement is used to group rows in a table based on specified columns. When using GROUP BY without aggregate functions, it's important to understand how it operates and the conditions it requires.

Consider the following example:

SELECT ename, sal
FROM emp
GROUP BY ename, sal;

This query will not return any rows because it violates the rules of GROUP BY without aggregate functions:

• Number of Columns in SELECT List: The number of columns in the SELECT list must equal the number of columns in the GROUP BY clause.

In this case, the SELECT list has two columns (ename, sal), but the GROUP BY clause has three (ename, sal, sal). To correct this, remove one sal column from the GROUP BY clause:

SELECT ename, sal
FROM emp
GROUP BY ename;

• Non-GROUP BY Columns in SELECT List: Non-GROUP BY columns can only be included in the SELECT list if they are constants or appear as arguments to aggregate functions.

When you include sal in the SELECT list, it becomes a non-GROUP BY column because it is not in the GROUP BY clause. Since it's not an aggregate function, it triggers the ORA-00979 error.

Correcting this requires either adding sal to the GROUP BY clause or using an aggregate function on it:

-- Add sal to the GROUP BY clause
SELECT ename, MIN(sal), MAX(sal)
FROM emp
GROUP BY ename, sal;

-- Use an aggregate function on sal
SELECT ename, SUM(sal)
FROM emp
GROUP BY ename;

Reasoning Behind the Rules

The GROUP BY operation merges rows with identical values in the GROUP BY columns, producing a single row for each unique set of values. Including non-GROUP BY columns in the SELECT list without aggregate functions creates ambiguity because it's unclear how to choose a representative value for each group.

For example, if you have a table with Name and Age columns and you GROUP BY Name, which age should be displayed in the result for each name? By using aggregate functions like MIN, MAX, or SUM, you explicitly specify how to handle such columns.

The above is the detailed content of How Does SQL's GROUP BY Clause Work Without Aggregate Functions?. For more information, please follow other related articles on the PHP Chinese website!