ORA-00979: not a GROUP BY expression 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 the SQL Error: ORA-00979: not a GROUP BY expression...

The tables used in the query are the following:

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 was asked several times already, but plz explain me trough this example.

Answer 1

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;

Answer 2

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 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, like c.recipe_name, appears in the SELECT clause.

Output:

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