Oracle Ora-00979 - "Not a Group by Expression"

ORA-00979 not a group by expression

You must put all columns of the SELECT in the GROUP BY or use functions on them which compress the results to a single value (like MIN, MAX or SUM).

A simple example to understand why this happens: Imagine you have a database like this:

FOO BAR
0   A
0   B

and you run SELECT * FROM table GROUP BY foo. This means the database must return a single row as result with the first column 0 to fulfill the GROUP BY but there are now two values of bar to chose from. Which result would you expect - A or B? Or should the database return more than one row, violating the contract of GROUP BY?

oracle sql error: ORA-00979: not a GROUP BY expression

GROUP BY is required for aggregating functions, such as SUM() or COUNT(). So a query like select * from politics group by WASDEPENDENT does not make sense.

Here is an example of the correct usage. This query counts how many records fall into each value of WASDEPENDENT:

select WASDEPENDENT, count(*) 
from politics 
group by WASDEPENDENT
order by WASDEPENDENT

i got error ORA-00979: not a GROUP BY expression

You are using GROUPING(d.department_id) but department_id is not in the GROUP BY clause.

You could add it to the GROUP BY clause:

SELECT d.department_name "department name",
       e.job_id "job title",
       SUM(e.salary) "monthly cost",
       GROUPING(d.department_id) "Department ID Used",
       GROUPING(e.job_id) "Job ID Used"
FROM   employees e
       JOIN departments d
       ON e.department_id=d.department_id
GROUP BY cube(d.department_name, d.department_id, e.job_id)
ORDER BY d.department_name, e.job_id

db<>fiddle here

ORA-00979: not a GROUP BY expression with a simple example

In the SELECT clause, you can only refer to expression that either appear in the GROUP BY clause or are aggregations (such as SUM). c.recipe_name does not qualify as such.

You might know that grouping by a.recipe_id will lead to a unique result for c.recipe_name (within each group). And Oracle might even be able to derive this information as well. But SQL is more strict and requires you to put the expression in the GROUP BY clause.

So, just write:

SELECT c.recipe_name, 
    COUNT(a.ingredient_id), 
    SUM(a.amount*b.ingredient_price)
FROM recipe_ingredients a
JOIN ingredients b
    ON a.ingredient_id = b.ingredient_id
JOIN recipes c
    ON a.recipe_id = c.recipe_id
GROUP BY a.recipe_id, c.recipe_name;

SQL Query not working ORA-00979: not a GROUP BY expression

You need to add firstname to your GROUP BY

GROUP BY firstname, lecturerID

You use an aggregate function (COUNT) on ModuleCode, so any remaining columns must either be aggregate functions (SUM/COUNT/MIN/MAX) or included in group by

(Is this a homework question?)

ORA-00979 Not a Group function error for query with User defined function in select statement

I am not sure how to use CTE with group by on user defined function. But I realized that I can rewrite the query to remove sub-query and CTE and make it simpler as following (and it works):

select * from ( 
    select a.*, ROWNUM rnum from
        (SELECT
            count(*) over() as COUNTS,
            GET_EVAULATED_VALUE(T.C_IMP, T.IMP) AS IMPORTANCE,
            count(*) AS NO_OF_PC_AFFECTED
        FROM TABLE_NAME T 
        WHERE T.ACNT_RELATION_ID = 16
        GROUP BY
            (GET_EVAULATED_VALUE(T.C_IMP, T.IMP))
        ORDER BY importance desc) a
    where ROWNUM <= 10 )
where rnum >= 0; 

---

Related Topics