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?

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

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

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 : select distinct(concat column) not a group by expression

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.

Try to make a view but have a problem ORA-00979: not a GROUP BY expression

You need to aggregate by the unaggregated columns n the select. And use JOIN!

CREATE VIEW VIEW_PROJECT AS
    SELECT p.Pname, d.Dname, COUNT(W.Essn) as Numb_Employees, SUM(W.Hours) as Total_Hours 
    FROM PROJECT P JOIN
         DEPARTMENT D
         ON P.Dnum = D.Dnumber JOIN
         WORKS_ON W 
         ON P.Pnumber = W.Pno 
GROUP BY Pname, Dname;