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

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 have three columns (colA, colB and colC). I want to have in my select result : the concatenation of column colA value and the two first value of column colB. and i want to group by my result. but i receive ORA-00979 not a group by expression error. How can i solve this ? Please help .

CodePudding user response：

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.