We help IT Professionals succeed at work.

on
Medium Priority
280 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

Top Expert 2006

Commented:
This gets you the values from the first table.  If you are using MySQL 4.1 or higher, you can use UNION and subqueries to get the results from all other tables/queries reported together.

SELECT SUM((QUANTITY * HMS_CARTON) /  1000000) AS Total_HMS_Carton FROM tblhorsham_exports_pack_details WHERE YEAR(date_column)='2004';

Commented:
Sorry forgot to mention i have two date columns, namely:

Date_From: and Date_To:

The Date_From column one contains all figures like 01/01/2004

and the Date_To column contains all figures like 31/12/2004

how can i improvise the above query to fetch data between these twp column dates?

Thanks!

Top Expert 2006
Commented:
SELECT SUM((QUANTITY * HMS_CARTON) /  1000000) AS Total_HMS_Carton FROM tblhorsham_exports_pack_details WHERE YEAR(Date_From)=2004 OR Year(Date_To)=2004 OR (YEAR(Date_From < 2004 AND Date_To > 2004));

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

Commented:
Hi there,

I have tried the above query in various different ways but with no luck.

at some occasions i get an error as follows:

Truncated incorrect datetime value:'0'                erroNr. 1292

any ideas?
This is the query i am trying according to the data i have in the Date columns:

SELECT SUM((QUANTITY * HMS_CARTON) /  1000000) AS Total_HMS_Carton FROM tblhorsham_exports_pack_details WHERE (Date(Date_From > 2004-01-01 AND Date_To < 2004-12-31));

The datatype for both columns is DATETIME thus i changed the YEAR to date as this seems to work.

All the data in Date_From is either 2004-01-01 or 2003-01-01

All the data in Date_To is either 2004-12-31  or 2003-12-31

Lastly there is a null time value in the records for each record, not sure if this is what is affecting the query. eg 2004-01-01 00:00:00

I hope i this helps.

Top Expert 2006

Commented:
Can you post:

SHOW CREATE TABLE tblhorsham_exports_pack_details;

Commented:
There you go:

'tblhorsham_exports_pack_details', 'CREATE TABLE `tblhorsham_exports_pack_details` (
`ID` int(11) NOT NULL auto_increment,
`ProductCode` int(11) default '0',
`Quantity` int(11) default NULL,
`Date_From` datetime default NULL,
`Date_To` datetime default NULL,
`HMP_Securitainer` float default NULL,
`HMP_Cap` float default NULL,
`HMP_Plastic_Bottle` float default NULL,
`HMP_Foil` float default NULL,
`HMP_Blisters_PVC` float default NULL,
`HMP_Tube_Plastic` float default NULL,
`HMP_Glass_Bottle` float default NULL,
`HMP_Ampoule(s)` float default NULL,
`HMP_Strip(s)_Alu/Alu` float default NULL,
`HMP Tube_Alu` float default NULL,
`HMS_Plastic_Tray` float default NULL,
`HMS_Carton` float default NULL,
`HMT_Overwrap` float default NULL,
`HMT_Cardboard_Outer` float default NULL,
`IMP_Securitainer` float default NULL,
`IMP_Metal_Cap` float default NULL,
`IMP_Cap` float default NULL,
`IMP_Plastic_Bottle` float default NULL,
`IMP_ALU_Blister` float default NULL,
`IMP_Strip(s)_Plastic` float default NULL,
`IMP_Tubes_Plastic` float default NULL,
`IMP_GLass_Bottle` float default NULL,
`IMP_Ampoule(s)` float default NULL,
`IMP_ALU_Strip` float default NULL,
`IMP_Tube_Alu` float default NULL,
`IMS_Plastic_tray` float default NULL,
`IMS_Carton` float default NULL,
`IMT_Overwrap` float default NULL,
`IMT_Cardboard_Outer` float default NULL,
`IMT_Plastic` float default NULL,
`IMT_Cardboard` float default NULL,
`IMT_Metal` float default NULL,
`IMT_Wood` float default NULL,
PRIMARY KEY  (`ID`),
KEY `ID` (`ID`),
KEY `ProductCode` (`ProductCode`),
KEY `ProductCode_2` (`ProductCode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1'

Commented:
Ok i got it to work somehow with the following query.

I also managed to use UNION to get two seperate values.

SELECT SUM((Quantity * HMS_Carton) / 1000000) As Total_HMS_Carton from tblhorsham_exports_pack_details where Date_from between '2004-01-01' and '2004-12-31' union
select sum((Quantity * HMS_Carton) / 1000000) As Total_HMS_CARTON_UK from tblhorsham_uk_pack_details where Date_From  between '2004-01-01' and '2004-12-31';

Now i need to know is how can i add these two values?

do i need to say SUM Total_HMS_Carton and  Total_HMS_CARTON_UK?

finally i only need the added value to be shown as a result and the previous intermediate values should not be shown on screen.

Top Expert 2006

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