SELECT
InnerCountsTable.CocktailID,
InnerCountsTable.CocktailName,
InnerCountsTable.CocktailShortName,
CountPresent,
CountMissing,
FORMAT(round((AVG(rating.Rating)*2))/2, 1) AS Rating,
(CountPresent - CountMissing) as Difference
FROM
(SELECT
cocktail.ID as CocktailID,
cocktail.Name as CocktailName,
cocktail.ShortName as CocktailShortName,
cocktail.Instructions as CocktailInstructions,
SUM(IF(userHas.NormalizedIngredientID IS NULL, 1, 0))
as CountMissing,
SUM(IF(userHas.NormalizedIngredientID IS NULL, 0, 1))
as CountPresent
FROM
mix INNER JOIN ingredient
ON mix.IngredientID = ingredient.ID
LEFT JOIN (
SELECT NormalizedIngredientID
FROM ingredient
WHERE ID=76
UNION
SELECT NormalizedIngredientID
FROM ingredient
WHERE ID=73) AS userHas
ON userHas.NormalizedIngredientID =
ingredient.NormalizedIngredientID
INNER JOIN cocktail ON mix.CocktailID = cocktail.ID
WHERE NOT (cocktail.Status = 'Cut')
GROUP BY mix.CocktailID) AS InnerCountsTable
LEFT JOIN rating
ON InnerCountsTable.CocktailID = rating.CocktailID
WHERE CountPresent > 0
GROUP BY InnerCountsTable.CocktailID
ORDER BY
CountMissing ASC,
Difference DESC,
CountPresent DESC LIMIT 30