mysql select an average from order by with a query

Question

I have 2 tables "recipes", "recipes_items" , in the table recipes_items i have an integer columns "important". Now I have a query that pulls the recipes ordered by the sum(important) of the recipes_items.important , the order by have a sub query :

(SELECT sum(...) from recipes_items...) DESC)

BUT what I want is to retrieve as additional column in the recipes query the average of the column "important" from table recipes_items How can that be ?

Thanks Moshe

select  recipes.recipeName,
        recipes.recipeID,
        AVG (recipes_items.importance) AS averageScore
    from  recipes,recipes_items
    WHERE  recipes_items.recipeID=recipes.recipeID
    ORDER BY  
      ( SELECT  SUM(recipes_items.importance) AS sumImportance
            FROM  recipes_items
            WHERE  recipes_items.recipeID = recipes.recipeID
              AND  recipes_items.itemID IN ('1059')
      ) DESC
    LIMIT  20

What i would like to get in results is order by the importance column from recipes_items.itemID that are matching a set of ID's in the 'IN()' section, also wanted to have the number or sum of the matching ID's in each row of a matching ID's to the recipes_items.itemID...

Answer 1

You don't show your full query but you can likely do this without a subquery and still include the average:

SELECT recipes.id, recipes.name, AVG(recipes_items.important)
FROM recipes
LEFT JOIN recipes_items ON (recipes.id = recipes_items.recipe_id)
GROUP BY recipes.id
ORDER BY SUM(recipes_items.important) DESC