CODEWITHSUNDEEP
sqloracle-databaseora-00979
lulu3131
lulu3131

Reputation: 25

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?

Upvotes: 1

Views: 276

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

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.

Upvotes: 0

Related Questions