MYSQL复杂的GROUP BY和SUM查询失败

Question

I have this convoluted query that is getting current inventory levels & converting the units to pounds, then looking at outstanding orders & converting the units to pounds, and finally display a summed amount needed of each ingredient for all outstanding orders.

The purpose is to list the ingredients with a inventory level and then an amount needed to fulfill all outstanding order. If the baker does not have enough flour then he can see this and look into sourcing more before he begins baking for the day.

The problem is then when I run this query it is skipping the first row in the group function. When I run it without grouping it gets all the rows correct, but when i run it with a SUM and GROUP BY it skips the first row so it under calculates that items amount needed value. Any tips? Thanks!

SELECT 
    inventory.id, 
    inventory.title, 
    products.weight, 
    products.id AS product_id, 
    (orders_items.quantity - orders_items.quantity_baked) AS quantity_to_be_baked,
    (SELECT @inventory_pounds:=
        CASE inventory.units
            WHEN  'kilograms'
            THEN 2.20462 * inventory.quantity
            WHEN  'pounds'
            THEN 1 * inventory.quantity
            WHEN  'ounces'
            THEN 0.0625 * inventory.quantity
            WHEN  'grams'
            THEN 0.00220462 * inventory.quantity
        END ) as inventory_pounds,
    (SELECT @dough_recipe_ingredient_pounds:=
        CASE dough_recipes.units
            WHEN 'kilograms'
            THEN 2.20462 * dough_recipes.amount
            WHEN 'pounds'
            THEN 1 * dough_recipes.amount
            WHEN 'ounces'
            THEN 0.0625 * dough_recipes.amount
            WHEN 'grams'
            THEN 0.00220462 * dough_recipes.amount
        END ) AS dough_recipe_ingredient_pounds, 
    (orders_items.quantity - orders_items.quantity_baked) AS num_loaves_needed,
    ( SELECT @dough_recipe_yield_pounds:=
        CASE doughs.units
            WHEN 'kilograms'
            THEN 2.20462 * doughs.yield
            WHEN 'pounds'
            THEN 1 * doughs.yield
            WHEN 'ounces'
            THEN 0.0625 * doughs.yield
            WHEN 'grams'
            THEN 0.00220462 * doughs.yield
        END ) AS dough_recipe_yield_pounds,
    (SELECT SUM( @dough_recipe_ingredient_pounds / @dough_recipe_yield_pounds * weight * (orders_items.quantity - orders_items.quantity_baked))) as amount_needed_for_orders
    FROM inventory
    LEFT JOIN dough_recipes ON inventory.id = dough_recipes.inventory_id
    LEFT JOIN products ON dough_recipes.dough_id = products.dough_id
    LEFT JOIN orders_items ON products.id = orders_items.product_id AND (orders_items.quantity - orders_items.quantity_baked) > 0
    LEFT JOIN doughs ON doughs.id = products.dough_id
    GROUP BY id

Answer 1

Group by does not skip rows or anything. Your query structure seems just wrong. Have a try with this:

SELECT SUM(dough_recipe_ingredient_pounds / dough_recipe_yield_pounds * weight * (quantity - quantity_baked))) as amount_needed_for_orders
FROM (
    SELECT 
    inventory.id, 
    inventory.title, 
    products.weight, 
    products.id AS product_id, 
    orders_items.quantity,
    orders_items.quantity_baked,
    (orders_items.quantity - orders_items.quantity_baked) AS quantity_to_be_baked,
        CASE inventory.units
            WHEN  'kilograms'
            THEN 2.20462 * inventory.quantity
            WHEN  'pounds'
            THEN 1 * inventory.quantity
            WHEN  'ounces'
            THEN 0.0625 * inventory.quantity
            WHEN  'grams'
            THEN 0.00220462 * inventory.quantity
        END as inventory_pounds,
        CASE dough_recipes.units
            WHEN 'kilograms'
            THEN 2.20462 * dough_recipes.amount
            WHEN 'pounds'
            THEN 1 * dough_recipes.amount
            WHEN 'ounces'
            THEN 0.0625 * dough_recipes.amount
            WHEN 'grams'
            THEN 0.00220462 * dough_recipes.amount
        END  AS dough_recipe_ingredient_pounds, 
    (orders_items.quantity - orders_items.quantity_baked) AS num_loaves_needed,
        CASE doughs.units
            WHEN 'kilograms'
            THEN 2.20462 * doughs.yield
            WHEN 'pounds'
            THEN 1 * doughs.yield
            WHEN 'ounces'
            THEN 0.0625 * doughs.yield
            WHEN 'grams'
            THEN 0.00220462 * doughs.yield
        END AS dough_recipe_yield_pounds
    FROM inventory
    LEFT JOIN dough_recipes ON inventory.id = dough_recipes.inventory_id
    LEFT JOIN products ON dough_recipes.dough_id = products.dough_id
    LEFT JOIN orders_items ON products.id = orders_items.product_id AND (orders_items.quantity - orders_items.quantity_baked) > 0
    LEFT JOIN doughs ON doughs.id = products.dough_id
) sq
GROUP BY id

If this is really the right query for what you're looking for, we can't tell without sample data and desired result. Above query is just a guess based on your query.