GROUP BY without aggregate function

Question

I am trying to understand GROUP BY (new to oracle dbms) without aggregate function.
How does it operate?
Here is what i have tried.

EMP table on which i will run my SQL.

SELECT ename , sal FROM emp GROUP BY ename , sal 

SELECT ename , sal   FROM emp   GROUP BY ename;   

Result

ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
Error at Line: 397 Column: 16

SELECT ename , sal   FROM emp   GROUP BY sal;   

Result

ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action: Error at Line: 411 Column: 8

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

Result

ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action: Error at Line: 425 Column: 8

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

So, basically the number of columns have to be equal to the number of columns in the GROUP BY clause, but i still do not understand why or what is going on.

Answer 1

That's how GROUP BY works. It takes several rows and turns them into one row. Because of this, it has to know what to do with all the combined rows where there have different values for some columns (fields). This is why you have two options for every field you want to SELECT : Either include it in the GROUP BY clause, or use it in an aggregate function so the system knows how you want to combine the field.

For example, let's say you have this table:

Name | OrderNumber ------------------ John | 1 John | 2 

If you say GROUP BY Name, how will it know which OrderNumber to show in the result? So you either include OrderNumber in group by, which will result in these two rows. Or, you use an aggregate function to show how to handle the OrderNumbers. For example, MAX(OrderNumber), which means the result is John | 2 or SUM(OrderNumber) which means the result is John | 3.

Answer 2

Given this data:

Col1  Col2  Col3  A     X     1  A     Y     2  A     Y     3  B     X     0  B     Y     3  B     Z     1 

This query:

SELECT Col1, Col2, Col3 FROM data GROUP BY Col1, Col2, Col3 

Would result in exactly the same table.

However, this query:

SELECT Col1, Col2 FROM data GROUP BY Col1, Col2 

Would result in:

Col1  Col2  A     X    A     Y    B     X    B     Y    B     Z   

Now, a query:

SELECT Col1, Col2, Col3 FROM data GROUP BY Col1, Col2 

Would create a problem: the line with A, Y is the result of grouping the two lines

 A     Y     2  A     Y     3 

So, which value should be in Col3, '2' or '3'?

Normally you would use a GROUP BY to calculate e.g. a sum:

SELECT Col1, Col2, SUM(Col3) FROM data GROUP BY Col1, Col2 

So in the line, we had a problem with we now get (2+3) = 5.

Grouping by all your columns in your select is effectively the same as using DISTINCT, and it is preferable to use the DISTINCT keyword word readability in this case.

So instead of

SELECT Col1, Col2, Col3 FROM data GROUP BY Col1, Col2, Col3 

use

SELECT DISTINCT Col1, Col2, Col3 FROM data