formulas in mysql forms 1

[compudude86]

hi all,
im migrating over from access to mysql, after finally figuring out access. i need to do a formula that automatically calculates two fields in a database and gives me a percentage. the formula is GPM=(srp-net)/srp . i got it to work, but its not doing it in order of operations. please help!!

 

[TonyGroves]

Sorry, you'll have to explain your problem a bit better.

 

[compudude86]

ok, heres my query:

CREATE VIEW `book`.`view` AS
SELECT Description, Size, Pack, List, Net, Discount, NetBtl, SRP, Vendor, Date, (SRP*NetBtl) / SRP as GPM FROM products;


now, i want to make "SRP" as a percent, and my formula is (SRP*NetBtl)/SRP, but it wont do it parenthesis first,it just mashes them all together

 

[TonyGroves]

Your formula is wrong. (SRP*NetBtl)/SRP is equal to NetBtl.

 

[compudude86]

ahh, thank you very much, i didnt catch that for some reason. now, is there a way to write in the query to make "GPM" a percent field? and how would i move the decimal?

 

[TonyGroves]

How about (srp-netbtl)*100/srp ?

 

[compudude86]

that could work, but i want it to be a regular number, no decimal, and a percent sign, i used to be able to do it in access, but i have no clue how to do it in mysql.

 

[TonyGroves]

[tt]CONCAT(ROUND((srp-netbtl)*100/srp),'%')[/tt]

 

[compudude86]

thank you so much, thats exactly what i was looking for!!! just one more quick one, is there a way to shave the last two digits off a field, i.e i have the price field, and its coming up as 119.0000, and i want it to be 119.00. how do i apply that to each field?

 

[TonyGroves]

[tt]ROUND(price,2)[/tt]

 

[compudude86]

now, i have to apply that to about 5 fields in the view, how would i put that into syntax?

 

[TonyGroves]

If you have more questions, please start separate thread(s).

 

[compudude86]

ok sorry about that