ORA-00979 Not a group by expression?

Question

I have the following code which throws an exception

Ora-00979: Not a group by expression.

Even if I remove the group by clause it still gives error

ORA-00937: not a single-group group function.

I am new in this field and do not have enough knowledge about joins etc. I'll appreciate if someone please help me with the following query.

select sDateTime AS Sale_Date, SUM(Amount) AS Sale, SerName 
FROM SALES natural join sales_Description natural join Services 
where ((sDateTime >= to_date('2015-01-01','yyyy-MM-dd')) 
  and (sDateTime <= to_date('2015-01-09','yyyy-MM-dd'))) 
  GROUP BY to_char(sDateTime,'yyyy-MM-dd')

`GROUP BY sDateTime ,SerName` That natural JOIN will byte you in the a@@ sooner or later in case some changes the column names — Mihai, Jan 10 '15 at 16:36

score 0 · Answer 1 · answered Jan 10 '15 at 17:00

0

The SerName column is not included in the GROUP BY clause. You have to include it in the GROUP BY clause or aggregate it (MAX, MIN, etc).

Try to change your query to this:

SELECT sDateTime AS Sale_Date, SUM(Amount) AS Sale, SerName 
FROM SALES natural join sales_Description natural join Services 
WHERE ((sDateTime >= to_date('2015-01-01','yyyy-MM-dd')) 
  AND (sDateTime <= to_date('2015-01-09','yyyy-MM-dd'))) 
GROUP BY to_char(sDateTime,'yyyy-MM-dd'), SerName

Anyway, I don't know if that's the result you want.

answered Jan 10 '15 at 17:00

dario

5,149
12
28
32

This is incorrect as expressions in group by and select do not match. Query should either change select or the group by condition. finally the correct query should be `SELECT to_char(sDateTime,'yyyy-MM-dd') AS Sale_Date, SerName, SUM(Amount) AS Sale FROM SALES natural join sales_Description natural join Services WHERE ((sDateTime >= to_date('2015-01-01','yyyy-MM-dd')) AND (sDateTime <= to_date('2015-01-09','yyyy-MM-dd'))) GROUP BY to_char(sDateTime,'yyyy-MM-dd'), SerName` – Antariksh Mar 03 '20 at 20:50

score 0 · Accepted Answer · answered Jan 10 '15 at 22:34

The first error not a group by is due to the following -

GROUP BY to_char(sDateTime,'yyyy-MM-dd'),.

You need to either have the same to_char in select or change the group by to sDateTime.

The second time you get the error not a single-group group error because you removed group by expression. You need to have the columns of the select list for which you are aggregating the rows.

Try the following -

    SELECT to_char(sDateTime,'yyyy-MM-dd') AS       Sale_Date, 
SerName, 
SUM(Amount) AS Sale
       FROM SALES natural join sales_Description natural join Services 
     WHERE ((sDateTime >= to_date('2015-01-01','yyyy-MM-dd')) 
      AND (sDateTime <= to_date('2015-01-09','yyyy-MM-dd'))) 
       GROUP BY to_char(sDateTime,'yyyy-MM-dd'), SerName

ORA-00979 Not a group by expression?

2 Answers2

Linked