CODEWITHSUNDEEP
mysqlsql
Jack
Jack

Reputation: 9784

MySQL: Using the value of two columns to calculate a third

I'm sure this is a common question but I can't find a solution for it in the way I'm describing. My query is like this:

SELECT 
    (
        SELECT SUM(finance_transaction_components.invoiced_price) 
        FROM finance_transactions
        JOIN finance_transaction_components ON finance_transaction_components.transaction_id = finance_transactions.id
        JOIN agents ON agents.id = finance_transactions.agent_id
        WHERE DATE_FORMAT(finance_transactions.completed_date, "%Y-%m-%d") BETWEEN "2013-10-01" AND "2013-10-29"
    ) AS "Total Costs",

    (
        SELECT SUM(finance_transactions.agent_price)
        FROM finance_transactions
        JOIN agents ON agents.id = finance_transactions.agent_id
        WHERE finance_transactions.product_id != 10
        AND DATE_FORMAT(completed_date, "%Y-%m-%d") BETWEEN "2013-10-01" AND "2013-10-29" 
    ) AS "Total Revenue",

    (
        SELECT SUM(finance_transactions.agent_price)
        FROM finance_transactions
        JOIN agents ON agents.id = finance_transactions.agent_id
        WHERE finance_transactions.product_id != 10
        AND DATE_FORMAT(completed_date, "%Y-%m-%d") BETWEEN "2013-10-01" AND "2013-10-29" 
        AND IF(finance_transactions.lldrg > 0, IF(finance_transactions.lldrg = 1, 0, 1) ,1) = 1
    ) - ( SELECT SUM(finance_transaction_components.invoiced_price) 
        FROM finance_transactions
        JOIN finance_transaction_components ON finance_transaction_components.transaction_id = finance_transactions.id
        JOIN agents ON agents.id = finance_transactions.agent_id
        WHERE DATE_FORMAT(finance_transactions.completed_date, "%Y-%m-%d") BETWEEN "2013-10-01" AND "2013-10-29"
    )   AS "Margin"

FROM finance_transactions
GROUP BY finance_transactions.agent_id

It doesn't really matter what the columns are etc. in this case, the important bit is this: the Margin column needs to repeat the Total Costs and Total Revenue queries to calculate, which seems like a silly way of doing it. But apparently I can't just say "Total Revenue" - "Total Costs" AS "Margin.

Upvotes: 0

Views: 807

Answers (2)

PinnyM
PinnyM

Reputation: 35533

UPDATE - This answer is based on the query from the original question.

As @X.L.Ant has proposed, you can wrap the query and do the simple math in the wrapper query. However, in your scenario, you can do this even more efficiently if you just simplify your query down to this:

SELECT agent_id, TotalCost, TotalRevenue, TotalRevenue - TotalCost AS Margin
FROM (
    SELECT finance_transactions.agent_id
           SUM(finance_transaction_totals.total_cost) AS TotalCost,
           SUM(CASE WHEN finance_transactions.product_id = 10 THEN 0 
                    ELSE finance_transactions.agent_price) AS TotalRevenue 
    FROM finance_transactions
    JOIN (SELECT transaction_id, SUM(invoiced_price) AS total_cost
          FROM finance_transaction_components
          GROUP BY transaction_id) finance_transaction_totals
       ON finance_transactions.id = finance_transaction_totals.transaction_id
    WHERE DATE_FORMAT(finance_transactions.completed_date, "%Y-%m-%d") BETWEEN "2013-10-01" AND "2013-10-29"
    GROUP BY finance_transactions.agent_id 
) t

Upvotes: 1

xlecoustillier
xlecoustillier

Reputation: 16351

You can achieve the same thing without repeating code simply by using an inner query:

SELECT TotalCosts, TotalRevenue, TotalCost - TotalRevenue AS Margin
FROM 
(
    SELECT 
    (
        SELECT SUM(finance_transaction_components.invoiced_price) 
        FROM finance_transactions
        JOIN finance_transaction_components ON finance_transaction_components.transaction_id = finance_transactions.id
        JOIN agents ON agents.id = finance_transactions.agent_id
        WHERE DATE_FORMAT(finance_transactions.completed_date, "%Y-%m-%d") BETWEEN "2013-10-01" AND "2013-10-29"
    ) AS TotalCosts,

    (
        SELECT SUM(finance_transactions.agent_price)
        FROM finance_transactions
        JOIN agents ON agents.id = finance_transactions.agent_id
        WHERE finance_transactions.product_id != 10
        AND DATE_FORMAT(completed_date, "%Y-%m-%d") BETWEEN "2013-10-01" AND "2013-10-29" 
    ) AS TotalRevenue
) t

EDIT: This answer is based on the first version of the question, the one with the aggregates functions.

Upvotes: 1

Related Questions