Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200

    Unanswered: Rolling up total to derive a value

    What happened? Where's the first post in this thread?

  2. #2
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39
    Good question.

    Here it is again.

    2 tables:

    table1 ( a lookup table ) 1:N
    id
    ingredient
    gross_cost
    ------------------------
    table2 ( one record per item )
    id
    rec_ingredient
    rec_unit ( a number )
    rec_portion ( a unit of measurement )
    rec_code ( unique identifier )



    Now, using various queries I can calculate values for table2 based on table1.

    I need to be able to tally up all the table2 cost values which I derive from using table1 gross_cost value, then use that total as a new value for one final calculation.

    I thought an join would do this but I'm thinking more and more it'll be a temp table.



    Pete

  3. #3
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39
    To further elaborate:

    The id fields are the primary keys and serve as row identifiers, the matching occurs on the ingredient/rec_ingredient field.

    The ingredient table stores basic data about an ingredient a chef may need in a recipe and is used to build a varying number of select boxes, each populated with the ingredients stored in the ingredient table. A chef chooses from them to build a recipe.

    The query I'm working on is to generate a food cost calculation based on the data input. The thing is that many of the ingredients vary in price from week to week, so calculating the food cost should be a derived value based on one gross_cost held in the ingredient table for one that ingredient. When an ingredient changes, I don't want to have to update each recipe_ingredient_data record where the ingredient occurs, but simply calculate based on the single gross_cost value stored in ingredient table. Thus the core information for any ingredient is stored but once, in the ingredient table.

    When a chef defines or builds a new recipe he enters the name of the recipe he's about to define and how many ingredients the recipe requires into a form. The number of ingredients he provides is used to build a form dynamically. The form offers X number of select boxes populated with the data in the ingredients table. The chef makes his selections, enters the portion amount and portion type, and a brief desrciption. This is stored in recipe_ingredient_data.

    Later, recipe_ingredient_data is queried, matching on ingredient.ingredient = rec_ingredient_data.rec_ingredient to determine a working food cost for the recipe.

    Something like:

    SELECT r.rec_ingredient AS Ingredient, i.gross_cost AS 'Bulk Price',

    round((( r.rec_unit * r.rec_portion ) * gross_cost) * 100) /100 AS 'Ingredient Cost',

    sum(round(((( r.rec_unit * r.rec_portion ) * gross_cost) * 100) /100)) * (r.rec_unit * r.rec_portion) * gross_cost AS '% of total'

    FROM recipe_ingredient_data r

    LEFT JOIN
    ingredient i ON i.ingredient = r.rec_ingredient

    WHERE r.rec_code = 'id3c9cb1714' //variable rec_code

    GROUP BY r.rec_ingredient

    ORDER BY r.rec_ingredient
    ---------------------------------

    Regards,

    Pete

  4. #4
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39

    Thumbs up Solution

    This is what I ended up implementing:

    I was unaware we could assign a variable in MySQL as here in the first SELECT @total_cost:=......

    Don't know how I missed that little ditty!!
    Code:
    SELECT @total_cost:=sum(round(r.rec_unit * r.rec_portion * gross_cost * 100) /100) from 
    FROM recipe_ingredient_data AS r 
    LEFT JOIN ingredient AS i 
    ON i.ingredient = r.rec_ingredient 
    WHERE r.rec_code = 'id3c9cb1714';
    
    SELECT r.rec_ingredient AS Ingredient, i.gross_cost AS 'Bulk Price', 
    round(r.rec_unit * r.rec_portion * gross_cost * 100) /100 AS 'Ingredient Cost', 
    round(r.rec_unit * r.rec_portion * gross_cost * 100 / @total_cost ) AS '% of total' 
    FROM recipe_ingredient_data AS r 
    LEFT JOIN ingredient AS i
    ON i.ingredient = r.rec_ingredient 
    WHERE r.rec_code = 'id3c9cb1714' ORDER BY r.rec_ingredient;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •