[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

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

0
Navgod
Asked:
Navgod
  • 4
  • 4
1 Solution
 
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now