title: MySQL: Using alias in select arithmetic - Unknown field
body: <p>Here's my select:</p>
  
  <pre><code>SELECT 
    SUM(t.amount + c.designFeeValue) as cashReceived, 
    ROUND(SUM(i.value) * (m.percentOurs / 100)) as adValue,
    m.managementFee as managementFee,
    m.productionCost as productionCost,
    5 as emailAddress,
    (
      (
        SELECT value
        FROM commission_transactions
        WHERE isDebit IS
        TRUE
      ) -
      (
        SELECT value
        FROM commission_transactions
        WHERE isDebit IS
        FALSE
      )
    ) as miscExpenses,
    (managementFee + productionCost + emailAddress + miscExpenses) as totalExpenses
  </code></pre>
  
  <p>This is bombing because of the following line, where I add up some aliases.</p>
  
  <pre><code>(managementFee + productionCost + emailAddress + miscExpenses) as totalExpenses
  </code></pre>
  
  <p>The aliases are unknown fields.  </p>
  
  <p>Is there a way I can keep the aliases for this arithmetic or do I need to re-do all the math that generates each alias for the calculation of totalExpenses?  That seems like a very ugly way to do it.</p>
  
  <hr>
  
  <p>UPDATE:</p>
  
  <p>Per your suggestions, I am now using a derived table.  </p>
  
  <pre><code>SELECT 
    cashReceived,
    adValue,
    managementFee,
    productionCost,
    emailAddress,
    miscExpenses,
    adValue + managementFee + productionCost + emailAddress + miscExpenses as totalExpenses
  FROM (
    SELECT
      SUM(t.amount + c.designFeeValue) as cashReceived,
      ROUND(SUM(i.value) * (m.percentOurs / 100)) as adValue,
      m.managementFee as managementFee,
      m.productionCost as productionCost,
      5 as emailAddress,
      (
        (
          SELECT value
          FROM commission_transactions
          WHERE isDebit IS TRUE
        ) -
        (
          SELECT value
          FROM commission_transactions
          WHERE isDebit IS FALSE
        )
      ) as miscExpenses  
    FROM magazines m
    JOIN insertions i ON i.magazineId = m.id
    JOIN transactions t ON t.insertionId = i.id
    JOIN contracts c ON i.contractId = c.id
    JOIN commission_transactions ct ON m.id = ct.magazineId
    WHERE m.id = 17
      AND t.isChargedBack IS FALSE
      AND t.`timestamp` &gt;= '2013-08-01 00:00:00'
      AND t.`timestamp` &lt; '2013-09-01 00:00:00'
      AND ct.createdDate &gt;= '2013-08-01 00:00:00'
      AND ct.createdDate &lt; '2013-09-01 00:00:00'
  ) sub;
  </code></pre>