We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

# Mysql Query calculation results manipulation with ColdFusion. Help!

on
Medium Priority
1,039 Views
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.

Thanks,

Nav

Comment
Watch Question

## View Solution Only

Commented:
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

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.
Commented:
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
...

Not the solution you were looking for? Getting a personalized solution is easy.

Commented:
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile