Reputation: 51
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
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
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