Prototype Prototype - 1 year ago 57
SQL Question

I got a Sum() where =, 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

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download