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

NavgodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

todd_farmerCommented:
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';
0
NavgodAuthor 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!

0
todd_farmerCommented:
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));
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

NavgodAuthor 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.

0
todd_farmerCommented:
Can you post:

SHOW CREATE TABLE tblhorsham_exports_pack_details;
0
NavgodAuthor 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'
0
NavgodAuthor 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.

         
0
todd_farmerCommented:
Glad you got it working.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

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.