MySQL: using values from a table to make math operations on another table

Question

HI let's pretend we have a tableA like this:

    +----+--------------------------+------------+
    | id | value                    | multiplier |
    +----+--------------------------+------------+
    |  1 | dataA                    | 10         |
    |  2 | dataB                    | 20         |

I want to take the data on the column "multiplier" in this case 10 and put it on another tableB like this

    +----+--------------------------+
    | id | to be divided /10        | to be divided by 20
    +----+--------------------------+
    |  1 | dataA/10                 | dataA/20  (corrected, was: dataB/20)
    |  2 | dataB/10                 | dataB/20

Pretending that the second table has many columns i need to do this for all the values in multiplier, one for each column on the second table.

You should look into pivot methods, like described here: http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns — Carsten Massmann, Jul 09 '15 at 14:23

score 0 · Accepted Answer · edited May 23 '17 at 11:51

This post is an application of the solution found here MySQL pivot table query with dynamic columns. My thanks go to @bluefeet.

First you need to collect all distinct divisors and create a columns list of of them, slighty embellished by some if and MAX magic. the result is stored in variable @sql.

Then you extend that column list @sql into a complete SELECT ... GROUP BY statement in which you JOIN the input table with itself (giving it alias names a and b). GROUPing is done over a.id,a.val.

The @sql string is turned into a prepared statement and eventually executed:

SELECT 
  GROUP_CONCAT(DISTINCT
    CONCAT('MAX(IF(b.divi = ''',divi,''', a.val/b.divi, NULL)) AS div_by_',divi )
  ) INTO @sql FROM tbl;

SET @sql = CONCAT('SELECT a.id, a.val,',@sql,
       ' FROM tbl a JOIN tbl b ON 1=1 GROUP BY a.id,a.val');

PREPARE stmt FROM @sql;
EXECUTE stmt;

see here http://www.sqlfiddle.com/#!9/e0bf7/1 for a working example.

data table tbl:

| id | val | divi |
|----|-----|------|
|  1 | 123 |   10 |
|  2 | 234 |   20 |
|  3 | 345 |   30 |

results:

| id | val | div_by_10 | div_by_20 | div_by_30 |
|----|-----|-----------|-----------|-----------|
|  1 | 123 |      12.3 |      6.15 |       4.1 |
|  2 | 234 |      23.4 |      11.7 |       7.8 |
|  3 | 345 |      34.5 |     17.25 |      11.5 |

Edit (solution for two input tables):

If I understood your comment right you might have wanted something like this:

tbla:         (denominator)  tblb:  (numerator)
| ida | vala | diva |        | idb | valb | 
|-----|------|------|        |-----|------| 
|   1 |  123 |   10 |        |   1 |  500 | 
|   2 |  234 |   20 |        |   2 |  600 | 
|   3 |  345 |   25 |        |   3 |  700 | 
|   4 |  111 |    5 |        |   4 |  800 | 
|   5 |  777 |    8 |

result:
| idb | valb | div_by_10 | div_by_20 | div_by_25 | div_by_5 | div_by_8 |
|-----|------|-----------|-----------|-----------|----------|----------|
|   1 |  500 |        50 |        25 |        20 |      100 |     62.5 |
|   2 |  600 |        60 |        30 |        24 |      120 |       75 |
|   3 |  700 |        70 |        35 |        28 |      140 |     87.5 |
|   4 |  800 |        80 |        40 |        32 |      160 |      100 |

This can be done with a slight variation of the above:

SELECT GROUP_CONCAT(DISTINCT
    CONCAT('MAX(IF(diva = ''',diva,''', valb/diva, NULL)) AS div_by_',diva )
  ) INTO @sql FROM tbla;

SET @sql = CONCAT('SELECT idb, valb,',@sql,' FROM tblb,tbla GROUP BY idb,valb');
PREPARE stmt FROM @sql; EXECUTE stmt;

see here: http://www.sqlfiddle.com/#!9/4b1f9/1

Unfortunately it seems like different from my question but i think it will work either way, because i've got 2 tables, and sevral colums to divide by all the way down — BlueStarry, Jul 10 '15 at 09:06

MySQL: using values from a table to make math operations on another table

1 Answers1