stan
stan

Reputation: 51

MySQL variable as result of subquery with elements of other table

I have a working query:

SELECT count(*),
       AVG(
             (SELECT sum(order_fills.quant * order_fills.price)
              FROM order_fills
              WHERE `type`='closed'
                AND order_fills.transaction_id = transactions.id) -
             (SELECT sum(order_fills.quant * order_fills.price)
              FROM order_fills
              WHERE `type`='opened'
                AND order_fills.transaction_id = transactions.id)) AS avgProfit,
       SUM(
             (SELECT sum(order_fills.quant * order_fills.price)
              FROM order_fills
              WHERE `type`='closed'
                AND order_fills.transaction_id = transactions.id)) AS volume,
FROM transactions
WHERE transactions.status='closed';

to make the code more readable I wish to set some parts of that query into variables.

I wish to achieve something like that (it does not work, returns null):

SET @transSells =
          (SELECT sum(order_fills.quant * order_fills.price)
           FROM order_fills
           WHERE type='closed'
             AND order_fills.transaction_id = transactions.id);


SET @transBuys =
          (SELECT sum(order_fills.quant * order_fills.price)
           FROM order_fills
           WHERE type='opened'
             AND order_fills.transaction_id = transactions.id);


SELECT avg(@transSells - @transBuys) AS `avgProfit`,
       sum(@transSells + @transBuys) AS `volume`
FROM transactions WHERE transactions.status='closed'

I think the problem might be that part of subquery WHERE statement is the result of the main query and another table (transactions.id):

AND order_fills.transaction_id = transactions.id

Upvotes: 0

Views: 42

Answers (2)

stan
stan

Reputation: 51

I found a slightly better solution for my case:

setting variables inside the query, and then using them in the same, single query.

 SET @comission = 0.03;

    SELECT count(*),
           AVG(
                 ( @transSells := (SELECT sum(order_fills.quant * order_fills.price)
                  FROM order_fills
                  WHERE `type`='closed'
                    AND order_fills.transaction_id = transactions.id)) 
                  -
                 ( @transBuys := (SELECT sum(order_fills.quant * order_fills.price)
                  FROM order_fills
                  WHERE `type`='opened'
                    AND order_fills.transaction_id = transactions.id))) AS avgProfit,

           SUM( @transSells ) AS volume,

           SUM( @transSells * @comission )

    FROM transactions
    WHERE transactions.status='closed';

Upvotes: 0

Nick
Nick

Reputation: 147196

The reason your variable query doesn't work is that transactions.id in the WHERE clause of your variable definitions is not defined (NULL), hence the queries won't return any results.

I think you can simplify your query by putting the variables into a derived table using conditional aggregation that you then JOIN to:

SELECT AVG(transSells - transBuys) AS avgProfit,
       SUM(transSells + transBuys) AS volume
FROM transactions
JOIN (SELECT transaction_id,
             SUM(CASE WHEN type='closed' THEN quant * price ELSE 0 END) AS transSells,
             SUM(CASE WHEN type='opened' THEN quant * price ELSE 0 END) AS transBuys
      FROM order_fills) o ON o.transaction_id = transactions.id
WHERE status = 'closed'

Upvotes: 1

Related Questions