Prototype Prototype - 3 months ago 10
SQL Question

I got a Sum() where a.name = b.name, I want to select the name of the highest Sum() : How?

I now I'm not clear with this question, but I have the following tables :

------------------------------
-- Recipe ----- Ingredients --
------------------------------
-- ID - ID --
-- Name - Name --
-- Cost - Cost --
-- Calories - Calories --
-- - RecipeName --
------------------------------


And I created this procedure

DELIMITER !!!
CREATE PROCEDURE RecipeCost()
BEGIN
SELECT Ingredients.RecipeName,
MAX(( SELECT SUM(Ingredients.Cost)
FROM Ingredients
WHERE Recipe.Name = Ingredients.RecipeName)) AS Price
FROM Ingredients
JOIN Recette
WHERE Recette.Name= Ingredients.RecipeName;

END !!!


It displays correctly the highest cost, but I can't manage to display the Name of the Recipe associated with this cost.

Do you have an idea why ?

Answer

The following gets the cost for each recipe:

select i.recipename, sum(i.cost)
from ingredients i
group by i.recipename;

To get the highest cost . . .

select i.recipename, sum(i.cost) as total_cost
from ingredients i
group by i.recipename
order by total_cost desc
limit 1;

The join to the recipes table is unnecessary, because the name is in the ingredients table.