[SOLVED] ORA-00979, but I have elements in my SELECT in my GROUP BY statement

ORA-00979, but I have elements in my SELECT in my GROUP BY statement

I have a query similar to this:

SELECT field1,
       field2,
       field3,
       field4,
       field5,
       field6,
       field7,
       field8,
       field9,
       count(field9)
  FROM REPORT_TABLE
       LEFT JOIN
       DATAMINE
         USING (REPORT_ID)
 WHERE field1 LIKE 'MatchingText%'
   AND TS_START between to_date('2015-05-01', 'YYYY-MM-DD') and to_date('2015-06-06', 'YYYY-MM-DD') + 0.99999999
GROUP BY field9,
         1,
         2,
         3,
         4,
         5,
         6,
         7,
         8,
         9;

When I reun it, I get

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"

I've seen this question, explaining that I need to include all fields in the select in the group by. I've done that by having the column numbers.

How can I adjust this query so that I don't get the 00979 error?

Solution

I've done that by having the column numbers.

That works in databases like PostgreSQL:

SELECT abc, xyz
FROM ...
GROUP BY 1, 2 -- referencing abc and xyz by column number

But not in Oracle. In Oracle, you cannot reference to your SELECT clause from your GROUP BY clause, but you have to repeat the full column expressions again

SELECT abc, xyz
FROM ...
GROUP BY abc, xyz

... or in your case:

[...]
GROUP BY 
    field1,
    field2,
    field3,
    field4,
    field5,
    field6,
    field7,
    field8,
    field9;