?
Solved

Mysql Query calculation results manipulation with ColdFusion. Help!

Posted on 2006-04-02
4
Medium Priority
?
1,022 Views
Last Modified: 2013-12-24
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

0
Comment
Question by:Navgod
  • 2
  • 2
4 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 16357828
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
 

Author Comment

by:Navgod
ID: 16361600
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
 
LVL 35

Accepted Solution

by:
mrichmon earned 1500 total points
ID: 16361789
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
 

Author Comment

by:Navgod
ID: 16362076
Thanks for all your help!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
The purpose of this video is to demonstrate how to insert an Iframe into WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Open Page or Post…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
Suggested Courses
Course of the Month15 days, 9 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question