MySQL calculate field value


Suppose I have two tables data_addon (fields: caption and val) and product (fields: …discount,cost, total_cost, qty).

On data_addon i have an entry… say caption as ‘multiplier’ and it’s val as ‘0.10’

What I want is that the field cost to automatically have the value of multiplier’s val i.e. here it’s 0.10 x qty.

So assume the product entry has qty of 100 then the cost should be 10. Ok once that is done there is something else i’d like but if the first one is explained I can probably figure out the the following by myself. Regardless:

I would also like the field total_cost to have the value cost - (cost x (discount/100)). So assume the value of discount for this particular product entry to be 30 then the total_cost will become 7 instead of 10.

I want this so that changing multiplier can automatically affect the cost of each product listings.

Any way to achieve this?

how do you expect to join these tables in the query? how do you pick which rows participate in the join?

as I said I’d like total_cost field of all entries in product table to automatically calculate the values. I want the fields cost and total_cost to automatically associate the values based on multiplier value in data_addon table and discount in product table. I figure it’s not possible. Mysql may not have excel like capabilities after all… or perhaps my question is still unclear… if so I’ll provide visual presentation.

I just though that if this is possible if I update the multiplier value then all other values will automatically be updated without having to update each of the entries etc… :slight_smile:

ok here is the visual presentation:

how do you know to pick “multiplier” rather than one of the other rows in the addon table?

and if you say “i just pick that row because i know it has the multiplier on it” then why do you need that table in the first place?

Because I know it should be there, want it to be there and “is” there. :slight_smile:

Because I want it to be there? :slight_smile:

Ok it seems you are having a hard time grasping what I want to achieve, so i’ll go into much more detail.

First off all the data_addon table is actually configuration table and the field ‘multiplier’ is actually ‘cost_per_credit’. And the products table is actually adCredit_packs.

I am making a advertising site, where users buys advertising credits and puts up their advertisement with them. Here each credit has a fixed value/cost. Here its $0.10. This is added in configuration table as ‘cost_per_credit’. I will be able to edit this and other such values (rows) via admin control panel I will be creating.

so on adCredit_packs (previously mentioned as products) contains all the same credits but as different packs, say 100 pack, 25 pack etc. I also will be able to set discounts for each pack through admin CP. The qty determines how much credits the user is to buy when buying that “pack”.

So suppose I believe I am not profiting enough or i am profiting too much, I could edit the cost per credit in configuration area of control panel and all credit pack cost will automatically be calculated without me having to run query and update them myself. My question is: “is this is possible, if yes - then how?”

If I still haven’t conveyed what I am aiming for then I give up, cause I don’t know how to explain it any other way :frowning: Thanks for trying to help me anyway.

let’s be fair and say that you had a hard time explaining it with a concocted example that obfuscated the true requirements

what you explained this time makes a lot more sense

so, yes, it’s possible, a configuration table, just like you described it


yes, I had hard time explaining it rather than you having hard time grasping it. ok sorry for a obfuscated explanation. It’s just that I didn’t want the entire site concept to be disclosed but then I thought it might be better than having people who are actually trying to help me for “free” to be confused because of that. :slight_smile:

So it is possible? So how do I get the cost and total_cost to have the calculated values? based on the ‘cost-per_credit’ on configuration table? :slight_smile:

the way to do it is simply to use the cost_per_credit value in a cross join (which is safe to do because the configuration table will have only one row) –

SELECT adCredit_packs.qty * configuration.cost_per_credit
           AS cost
  FROM configuration
  JOIN adCredit_packs