Mysql Query calculation results manipulation with ColdFusion. Help!

Hello there,

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:

tblhorsham_exports_pack_details
tblhorsham_UK_pack_details
tblhorsham_UK_Direct_pack_details

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.

for example

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.

Thanks,

Nav

NavgodAsked:
Who is Participating?
 
mrichmonCommented:
You can't with what you are doing.  The inner queries are all being unioned.  That means the results will be like this:

v
----------
25475
973565
36475
274856
37596
etc.


Then you select the rounded sum of all of them.  SO the final result of that is just a single value.

So you can only show the results of the query which are a single value.  You would do that like this:

#queryname.aliasname#

where you would have to assign an alias to the rounded sum like this in order to display it:
SELECT ROUND(SUM(v))  AS aliasname FROM
...
0
 
mrichmonCommented:
It would help to see what you used in access because there does not appear to be enough information about all the tables.  Also if it worked before it may be easier to convert that to come up with what you want from scratch
0
 
NavgodAuthor Commented:
Ok i have got the query working, the following is the query:

SELECT ROUND(SUM(v))  FROM
(SELECT SUM((Quantity * HMS_Carton) / 1000000) as v from tblhorsham_exports_pack_details
where Date_from between '2004-01-01' and '2004-12-31'
union
SELECT SUM((Quantity * HMS_Carton) / 1000000) as v from tblhorsham_uk_pack_details
where Date_From  between '2004-01-01' and '2004-12-31'
union
SELECT SUM((Quantity * HMS_Carton) / 1000000) as v from tbluk_direct_pack_details
where Date_from between '2004-01-01' and '2004-12-31'
union
SELECT SUM((Quantity * HMT_Cardboard_outer) / 1000000) as v from tblhorsham_exports_pack_details
where Date_from between '2004-01-01' and '2004-12-31'
union
SELECT SUM((Quantity * HMT_Cardboard_outer) / 1000000) as v from tblhorsham_uk_pack_details
where Date_from between '2004-01-01' and '2004-12-31'
union
SELECT SUM((Quantity * HMT_Cardboard_outer) / 1000000) as v from tbluk_direct_pack_details
where Date_from between '2004-01-01' and '2004-12-31'
) AS tmp;


That is just one of 25 different queries which do a similar calculation. How can i effectively and easily show these values in ColdFusion?

Thanks.
0
 
NavgodAuthor Commented:
Thanks for all your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.