Search code examples
mysqldatabasesequelpro

Mathematical operations using MySQL

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.

Solution

  • 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 ;-)