Mathematical operations using MySQL

Question

I want to do a couple of mathematical operations using different columns from different tables.

Say I have two tables A & B:

A

Value        Size    Pack    
-----------------------------------------
234567       10      1      
234589       20      1       
234561       30      2

B

Value        Quantity   Amount   
-----------------------------------------
234567       5          200       
234589       10         300      
234561       8          150

Now I would like to know how to do the following:

NEWCOLUMN (placed into B table) = Amount / (Size * Pack * Quantity)

and display the result on a new column in table B.

I've tried to left join both tables in order to get all columns into one table but I don't know where to go from there. Also, my PK is indeed value and I suppose I have to add a FK in order for it to work?

This is a sample of what I have so far:

SELECT * 
FROM `B` 
LEFT JOIN `A` 
ON `B`.`Value`=`A`.`Value`

I've tried researching this in multiple websites but couldn't find a definite answer.

score 0 · Answer 1 · answered May 11 '15 at 04:43

Here's the Select Statement for the mathematical operation. Working Example : http://sqlfiddle.com/#!9/83e52/7

#Table A is Test, Table B is Test1

SELECT Test.Value, (Test1.Amt / (Test.Size * Test.Pack * Test1.Qty)) as New_Column
FROM `Test1` 
LEFT JOIN `Test` 
ON `Test1`.`Value`=`Test`.`Value`

If you find the result correct then use an Insert Statement to insert these values into a column in Table B.

P.S. Please provide a sqlfiddle in future, it helps.

score 0 · Accepted Answer · answered May 11 '15 at 04:43

0

Did you try that?

SELECT *, (`B`.`Amount` / (`A`.`Size` * `A`.`Pack` * `B`.`Quantity`)) as `NEWCOLUMN`
FROM `B` 
LEFT JOIN `A` 
ON `B`.`Value`=`A`.`Value`

NEWCOLUMN won't be a "new column" in table B but you will get the intended value in your results. There's no real point in storing this in a column (actually I wonder why you have 2 tables in the first place).

It's also not specific to SequelPro which is only a MySQL client ;-)

answered May 11 '15 at 04:43

Capsule

6,118
1
20
27

Data was given in different files and in one of them, table A, has unique data while table B has duplicates. Seems like what you typed worked! Thanks for your help. – theduker May 11 '15 at 05:09
Glad it did. You don't need any foreign key btw, since `Value` is the Primary key in both your tables, the joint is already optimised. – Capsule May 11 '15 at 05:12
Is there anyway to display composite primary key from table B in the results? For example, I have it to where 234567 is 1 then 234567 is 2 (where 1 and 2 is column Sequence). I want to be able to identify which is which in the results. Hope this makes sense. – theduker May 11 '15 at 05:26
Sorry it doesn't make much sense :-) You're trying to identify the table associated with the two `Value` columns in your results? – Capsule May 11 '15 at 05:33
Say in Table B, "Value" has like millions of duplicates so I had to create a composite primary key [with column "Seq" (1,2,3,4...n) and column "Value"]. When I run the query the results only display column "Value". Is there any way to display the column "Seq" in there as well? I know it's hard to visualize since I didn't put in the question but hope this clarifies things a bit more. – theduker May 11 '15 at 05:38
Awesome, instead of putting SELECT *, I put a specific column so I changed it back to * and now everything works! – theduker May 14 '15 at 17:11
You could just add ```B`.`Seq``` if you don't want to use `*` – Capsule May 15 '15 at 11:49

Mathematical operations using MySQL

2 Answers2