[SOLVED] ORA-00979 : select distinct(concat column) not a group by expression

ORA-00979 : select distinct(concat column) not a group by expression

I am getting ORA-00979 with the following query:

SELECT 
 DISTINCT ((colA || SUBSTR(colB, 1, 2))) AS colA 
FROM myTable 
WHERE colC='5678'
GROUP BY colA

I have a table called myTable which has three columns (colA, colB and colC).
I want to have in my select result: the concatenation of column colA value and the two first values of column colB. Furthermore, I want to group by my result. However, I receive an ORA-00979 and not a group by expression error.
How can I solve this?

Solution

You may repeat the entire expression in the GROUP BY clause as it appears in the SELECT:

SELECT DISTINCT colA || SUBSTR(colB, 1, 2) AS colA
FROM myTable
WHERE colC = '5678'
GROUP BY colA || SUBSTR(colB, 1, 2);

Oracle does not supporting grouping using aliases, or using 1, 2, etc. positional parameters. And note that DISTINCT is not a SQL function.