Mysql Query calculation results manipulation with ColdFusion. Help!
Posted on 2006-04-02
I have two issues.. one regarding calculating some values in my mysql tables and secondly how i can display these with CF code.
I have identical tables with different data in mysql as named below:
There are 6 main values which are needed from the above tables by the calculations shown below.#
1.From table tblhorsham_exports_pack_details all the data for year 2004 is picked and the values from the field “quantity” are multiplied by the values from the field “HMS_carton”. All the results got from this calculation are then converted into tonnes by dividing each result by 1000000.Once all these values are derived, they are all added up together to get their sum and one final figure which we can call lets say Total HMS_Carton for Horsham Exports.
QUANTITY | HMS_CARTON| GENERATED RESULT BY QUERY AFTER MULTIPLICATION| LATER THE RESULT IS DIVIDED BY 1000000|
2356 | 0.2| 471.2| 0.0004712|
12356 | 0.26| 3212.56| 0.003212.56|
and sooooo on with many other records..
Finally i the results from the fourth column are added to get the one of the six values: (0.0004712 + 0.003212.56) = 0.00368376
The same above procedure is repeated 5 other times with similar fields from the other tables.
Finally we would have 6 figures which are then to be added up to arrive at the FINAL VALUE!
Now, i need to know how to do these calculations in Mysql and use CF to display this final value! I have no idea how to do this as i did this with ease previously in MS Access with query tables and simple calculation queries.
Can someone please help me with this.