MySQL: Make calculated column from multiple tables with complicated equation

Question

I wrote a formula in PHP that uses fields from different tables to calculate percentage. What i want to try and accomplish is translating it into SQL, if it's possible.

I understand that i can use SET to make local variables, then SELECT that variable to display in my final table, but setting those variables as equations is giving me some hassle.

Here's the PHP. I tried to make it as simple as possible without being confusing.

/*Setting fields to variables for ease*/
$PU = {table1.PU}*60;
$PD = {table1.PD};
$UD = {table3.UD};
$GP = {table1.GP};
$S = {table2.S};
$R = {table4.R};
$PR = {table1.CT}/60;

/*Combining variables*/
$TRT = $PU - $PD - $UD;
$PT = $TRT * $PR;
$TP = $GP + $S + $R;


/*Checking and correcting for zero division plus final calculation*/
if ($PU == 0 || $PT == 0 || $TP == 0){
    {complete} = "Error!";
}
else {
$a = $TRT/$PU;
$b = $TP/$PT;
$c = $GP/$TP;
{complete} = 100 * ($a * $b * $c);
}

And the MySQL i have started. My plan of attack was to assign the formulas to variables using the column names, including checking for zero-division. Running this through SQL Maestro, giving the error on invalid SET token. I've tried wrapping the whole thing in double quotes (and checked @variable $variable, =, := all with no luck) and checked several sources, my SET command should be correct. My private keys and foreign keys are set correctly.

SET @a = (((table1.PU*60) - table1.PD - table3.UD) / (table1.PU*60));
SET @b = (table1.GP + table2.S + table4.R)/(((table1.PU*60) - table1.PD - table3.UPD)*(table1.CT/60));
SET @c = table1.GP/(table1.GP + table2.S + table4.R);
SET @complete = 100*(@a*@b*@c);

IF(table1.PU = 0, 'Error!',@a),
IF(table1.CT = 0, 'Error!',@p),

SELECT
  table1.PU,
  table1.PD,
  table1.CT,
  table2.S,
  table3.UD,
  table4.R,
  @complete
FROM
  table1
  LEFT JOIN table2
  LEFT JOIN table3
  LEFT JOIN table4

The final result i'm looking for is to display all the individual fields i'm selecting, as well as the calculated field. I've looked into PROCEDURE and i think that might be beneficial but i am unsure of how to incorporate it fully.

How are the tables joined? I assume a simple LEFT JOIN wouldn't help you. You need to specify the exact condition how the tables are joined — Reeno, Apr 14 '15 at 21:38
You use a variable $UD but never define it and variable $UPD is never used. I assume they're the same. — Reeno, Apr 14 '15 at 21:46
The tables are joined through a simple LEFT JOIN, i left that out because i didn't think it was necessary to complete the calculations. The tables all contain multiple rows. $UPD is supposed to be $UD, that was a typo on my part, sorry! — Mike Greene, Apr 15 '15 at 13:15

score 0 · Answer 1 · edited May 23 '17 at 12:28

I think SET works with select queries only: How to set variable from a SQL query?

Furthermore, I assumed table1-4 only contain a single row. Otherwise, SET doesn't work.

SET @a = (SELECT 
           (((table1.PU*60) - table1.PD - table3.UPD) / (table1.PU*60)) 
          FROM table1, table3);
SET @b = (SELECT 
           (table1.GP + table2.S + table4.R)/(((table1.PU*60) - table1.PD - table3.UPD)*(table1.CT/60)) 
          FROM table1, table2, table3, table4);
SET @c = (SELECT 
           table1.GP/(table1.GP + table2.S + table4.R) 
          FROM table1, table2, table4);
SET @complete = 100*(@a*@b*@c);

IF(table1.PU = 0, 'Error!',@a),
IF(table1.CT = 0, 'Error!',@p),

SELECT
    table1.PU,
    table1.PD,
    table1.CT,
    table2.S,
    table3.UPD,
    table4.R,
    @complete // or perhaps (SELECT @complete as complete)
FROM
  table1, table2, table3, table4

Does SET strictly work with single row tables? Or is just how you have it written? — Mike Greene, Apr 15 '15 at 13:14
SET works with single row tables only. But you can use a different syntax: SELECT @a=(((table1.PU*60) - table1.PD - table3.UPD) / (table1.PU*60)) FROM table1, table3; In this case, @a is set by the last row in the table. — moo, Apr 15 '15 at 15:59

score 0 · Accepted Answer · answered Apr 14 '15 at 21:44

You don't need SET for this. Here's a simple SELECT which makes all the calculations:

SELECT
    table1.PU * 60 AS PU,
    table1.PD AS PD,
    table3.UPD AS UPD,
    table1.GP AS GP,
    table2.S AS S,
    table4.R AS R,
    table1.CT/60 AS PR,
    (table1.PU * 60) - table1.PD - table3.UPD AS TRT,
    (table1.PU * 60 - table1.PD - table3.UPD) * (table1.CT/60) AS PT,
    table1.GP + table2.S + table4.R AS TP,
    ((table1.PU * 60) - table1.PD - table3.UPD) / (table1.PU * 60) AS a,
    (table1.GP + table2.S + table4.R) / ((table1.PU * 60 - table1.PD - table3.UPD) * (table1.CT/60)) AS b,
    table1.GP / (table1.GP + table2.S + table4.R) AS c,
    100 * (((table1.PU * 60) - table1.PD - table3.UPD) / (table1.PU * 60)) * ((table1.GP + table2.S + table4.R) / ((table1.PU * 60 - table1.PD - table3.UPD) * (table1.CT/60))) * (table1.GP / (table1.GP + table2.S + table4.R)) AS complete
FROM
    table1,
    table2,
    table3,
    table4

Assuming all your tables only contain one row. Otherwise you need to specify some JOIN conditions.

Perfect, thanks! I was not aware you could just include the calculations directly in the SELECT. — Mike Greene, Apr 15 '15 at 13:11

MySQL: Make calculated column from multiple tables with complicated equation

2 Answers2