PDA

View Full Version : Rolling up total to derive a value

Paul
03-25-02, 14:56
What happened? Where's the first post in this thread?
phpPete
03-25-02, 16:31
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
phpPete
03-26-02, 07:38
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
phpPete
03-26-02, 12:25
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!!

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;