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

Question

I tried the following example in Oracle 11g: http://joshualande.com/filters-joins-aggregations/

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; 

I get SQL error: ORA-00979: not a GROUP BY expression...

The tables used in the query are as follows:

 CREATE TABLE recipes ( recipe_id INT NOT NULL, recipe_name VARCHAR(30) NOT NULL, PRIMARY KEY (recipe_id), UNIQUE (recipe_name) ); INSERT INTO RECIPES (RECIPE_ID, RECIPE_NAME) VALUES (1, 'Tacos'); INSERT INTO RECIPES (recipe_id, recipe_name) VALUES (2, 'Tomato Soup'); INSERT INTO RECIPES (recipe_id, recipe_name) VALUES (3, 'Grilled Cheese'); CREATE TABLE ingredients ( ingredient_id INT NOT NULL, ingredient_name VARCHAR(30) NOT NULL, ingredient_price INT NOT NULL, PRIMARY KEY (ingredient_id), UNIQUE (ingredient_name) ); INSERT INTO ingredients (ingredient_id, ingredient_name, ingredient_price) VALUES (1, 'Beef', 5); INSERT INTO ingredients (ingredient_id, ingredient_name, ingredient_price) VALUES (2, 'Lettuce', 1); INSERT INTO ingredients (ingredient_id, ingredient_name, ingredient_price) VALUES (3, 'Tomatoes', 2); INSERT INTO ingredients (ingredient_id, ingredient_name, ingredient_price) VALUES (4, 'Taco Shell', 2); INSERT INTO ingredients (ingredient_id, ingredient_name, ingredient_price) VALUES (5, 'Cheese', 3); INSERT INTO ingredients (ingredient_id, ingredient_name, ingredient_price) VALUES (6, 'Milk', 1); INSERT INTO ingredients (ingredient_id, ingredient_name, ingredient_price) VALUES (7, 'Bread', 2); CREATE TABLE recipe_ingredients ( recipe_id int NOT NULL, ingredient_id INT NOT NULL, amount INT NOT NULL, PRIMARY KEY (recipe_id,ingredient_id) ); INSERT INTO recipe_ingredients (recipe_id, ingredient_id, amount) VALUES (1,1,1); INSERT INTO recipe_ingredients (recipe_id, ingredient_id, amount) VALUES (1,2,2); INSERT INTO recipe_ingredients (recipe_id, ingredient_id, amount) VALUES (1,3,2); INSERT INTO recipe_ingredients (recipe_id, ingredient_id, amount) VALUES (1,4,3); INSERT INTO recipe_ingredients (recipe_id, ingredient_id, amount) VALUES (1,5,1); INSERT INTO recipe_ingredients (recipe_id, ingredient_id, amount) VALUES (2,3,2); INSERT INTO recipe_ingredients (recipe_id, ingredient_id, amount) VALUES (2,6,1); INSERT INTO recipe_ingredients (recipe_id, ingredient_id, amount) VALUES (3,5,1); INSERT INTO recipe_ingredients (recipe_id, ingredient_id, amount) VALUES (3,7,2); 

I know this question has been asked several times, but I will explain it in this example.

Answer 1

In the SELECT clause, you can only refer to an expression that either appears in the GROUP BY clause or is an aggregation (for example, SUM). c.recipe_name cannot be considered as such.

You may know that grouping a.recipe_id will produce a unique result for c.recipe_name (within each group). And Oracle can even get this information. But SQL is more strict and requires that you put this expression in a 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; 

Answer 2

You should also put the c.recipe_name field in the GROUP BY :

 SELECT c.recipe_name, COUNT(a.ingredient_id) AS cnt, SUM(a.amount*b.ingredient_price) AS sum 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 c.recipe_name, a.recipe_id; 

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

Output:

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

Answer 3

 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 c.recipe_name