asked on

Mysql Data Calculation queries! Please 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

todd_farmer

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';

Navgod

ASKER

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!

ASKER CERTIFIED SOLUTION

todd_farmer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Navgod

ASKER

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.

todd_farmer

Can you post:

SHOW CREATE TABLE tblhorsham_exports_pack_details;

Navgod

ASKER

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'

Navgod

ASKER

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.

todd_farmer

Glad you got it working.