Hello,
I am fighting little bit with GROUP BY expression.
After some tests I am able to reduce the problem to following...
When can I use column numbers in GROUP BY expression?
Consider this situation:
CREATE TABLE EMP4 (
NAME VARCHAR2(10)
);
COMMIT;
INSERT INTO EMP4 VALUES('Tamara');
INSERT INTO EMP4 VALUES('John');
INSERT INTO EMP4 VALUES('Joseph');
COMMIT;
SELECT NAME, COUNT(*)
FROM EMP4
GROUP BY 1;
00979. 00000 - "not a GROUP BY expression"
-- This is working
SELECT NAME, COUNT(*)
FROM EMP4
GROUP BY NAME;
Why is the GROUP BY 1 not workig?
I am using the GROUP BY 1 because in real query there is some PL/SQL function which somehow modifies the column NAME, so I can't use the column name
SELECT TEST_PACKAGE.AppendSomeCharacter(NAME), COUNT(*)
FROM EMP4
GROUP BY 1;
Of course I can nest the query and move the COUNT and GROUP BY to outer query or maybe something else, but I was just curious why is the GROUP BY not working...
Also in real query, there are 3 columns in the GROUP BY expression, so I have there GROUP BY 1, 2, 3
Thanks for help