How Does GROUP BY Work Without Aggregate Functions in SQL?

Group By without Aggregate Functions: Understanding the Mechanism

GROUP BY is a powerful SQL clause that allows us to group and summarize data based on specified columns. While aggregate functions like SUM, COUNT, and AVG are typically used with GROUP BY, it's also possible to use GROUP BY without aggregate functions. Understanding this concept is crucial for leveraging the full capabilities of GROUP BY.

When using GROUP BY without aggregate functions, it's essential to understand that each group will result in a single row in the output. The values for columns not included in the GROUP BY clause will be effectively duplicated for every row in the group.

Consider the EMP table:

ename | sal
-------+------
Smith  | 1000
Jones  | 1200
Davis  | 1100
Wilson | 1200
Brown  | 1300

Scenario 1: Invalid GROUP BY Expression

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

This query fails with the error ORA-00979 because sal is not included in the GROUP BY clause. Every row would have a different value for sal, resulting in multiple rows for each employee.

Scenario 2: Valid GROUP BY Expression

SELECT ename, sal
FROM emp
GROUP BY ename;

This query is valid because it groups the rows by ename, which ensures a unique row for each employee. The values for sal will be duplicated for each row belonging to the same employee.

Scenario 3: Invalid GROUP BY Expression

SELECT ename, sal
FROM emp
GROUP BY sal;

This query is invalid because it attempts to group the rows by sal, which would result in multiple rows for employees with the same salary.

Scenario 4: Invalid GROUP BY Expression

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

This query is invalid because the order of columns in the GROUP BY clause must match the order of columns in the SELECT clause.

Scenario 5: Valid GROUP BY Expression

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

This query is valid because the order of columns in the GROUP BY clause matches the order of columns in the SELECT clause. The result will contain a single row for each distinct combination of empno, ename, and sal.

In summary, GROUP BY without aggregate functions removes duplicates from the specified columns by grouping rows that share the same values. However, all columns not included in the GROUP BY clause will have duplicated values in the output rows.

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