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

Question

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?

score 7 · Accepted Answer · answered Jun 05 '15 at 14:27

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;

score 0 · Answer 2 · answered Jun 05 '15 at 14:41

I believe you should just be able to group by non counting or aggregate fields and get not get an error. You may also need to add a naming convention since you are counting field9 and also counting by it

SELECT 
field1,
field2,
field3,
field4,
field5,
field6,
field7,
field8,
field9,
count(field9)  AS "Field9_Count"
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 field1, field2, field3, field4, field5, field6, field7, field8, field9

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

2 Answers2