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 aJOIN ingredients b    ON a.ingredient_id = b.ingredient_idJOIN recipes c    ON a.recipe_id = c.recipe_idGROUP BY a.recipe_id, c.recipe_name;

You have to also put field c.recipe_name in the GROUP BY clause:

SELECT c.recipe_name,        COUNT(a.ingredient_id) AS cnt,        SUM(a.amount*b.ingredient_price) AS sumFROM recipe_ingredients aJOIN ingredients b  ON a.ingredient_id = b.ingredient_idJOIN recipes c  ON a.recipe_id = c.recipe_idGROUP BY c.recipe_name, a.recipe_id;

The problem with your query is that a non-aggregated column, like c.recipe_name, appears in the SELECT clause.

Output:

recipe_name     cnt sum------------------------Grilled Cheese  2   7Tacos           5   20Tomato Soup     2   5

SELECT     c.recipe_name,     COUNT(a.ingredient_id),     SUM(a.amount*b.ingredient_price)FROM recipe_ingredients aJOIN ingredients b  ON a.ingredient_id = b.ingredient_idJOIN recipes c  ON a.recipe_id = c.recipe_idGROUP BY c.recipe_name