CODEWITHSUNDEEP
mysql
Souvick Dey
Souvick Dey

Reputation: 11

how to calculate within a procedure by the result of two queries

I have a procedure called ProfitCalculation. Which takes three arguments..the procedure body is given below:

delimiter //

CREATE PROCEDURE ProfitCalculation
     (
        IN   instrument                      INT(20)    , 
        OUT  buy                             float(10,3)  ,
        OUT  sell                            float(10,3)    ,
        OUT  profit                          float(10,3)   
     )
BEGIN 

    SELECT SUM(FldLastTradeQuantity*FldPrice) as total_buy
    INTO   buy                              
    FROM   TblOrders
    WHERE  FldInstrumentID = instrument AND FldBuySell = 'b' AND FldLastTradePrice != 0 AND FldLastTradeQuantity != 0 group by FldInstrumentID;


    SELECT SUM(FldLastTradeQuantity*FldPrice) as total_buy
    INTO   sell                      
    FROM   TblOrders
    WHERE  FldInstrumentID = instrument AND FldBuySell = 's' AND FldLastTradePrice != 0 AND FldLastTradeQuantity != 0 group by FldInstrumentID; 

     profit = sell - buy;  

END
delimiter ;

Now I want to calculate the profit.but it returns only 1. please give me a solution which will return the actual result.

Upvotes: 0

Views: 313

Answers (2)

Devart
Devart

Reputation: 122002

Try this code -

...
BEGIN

SELECT
  SUM(IF(FldBuySell = 'b', FldLastTradeQuantity * FldPrice, 0)) AS total_buy,
  SUM(IF(FldBuySell = 's', FldLastTradeQuantity * FldPrice, 0)) AS total_sell
  INTO buy, sell
FROM
  TblOrders
WHERE
  FldInstrumentID = instrument
  AND FldLastTradePrice != 0
  AND FldLastTradeQuantity != 0;

SET profit = sell - buy;

END
...

Edit:

Try to run this code pattern in MySQL Command-Line Tool or in another tool and check results -

SET @instrument = 10;
SET @buy = NULL;
SET @sell = NULL;
SET @profit = NULL;

CALL ProfitCalculation(@instrument, @buy, @sell, @profit);

SELECT @buy, @sell, @profit;

Edit 2:

DELIMITER $$

CREATE PROCEDURE test(OUT buy FLOAT(10, 3), OUT sell FLOAT(10, 3), OUT profit FLOAT(10, 3))
BEGIN
  SELECT 10, 15 INTO buy, sell; -- variant with SELECT INTO
  SET profit = sell - buy;
END$$

DELIMITER ;

SET @buy = NULL;
SET @sell = NULL;
SET @profit = NULL;

CALL test(@buy, @sell, @profit);

SELECT @buy, @sell, @profit;

+------+-------+---------+
| @buy | @sell | @profit |
+------+-------+---------+
|   10 |    15 |       5 |
+------+-------+---------+

Profit is not NULL.

Upvotes: 0

a'r
a'r

Reputation: 37009

This procedure can be reduced to a single SQL query, which might help you with you investigation.

SELECT buy, sell, (sell - buy) profit FROM (
    SELECT 
        SUM(CASE FldBuySell WHEN 'b' THEN FldLastTradeQuantity*FldPrice END) buy,
        SUM(CASE FldBuySell WHEN 's' THEN FldLastTradeQuantity*FldPrice END) sell
    FROM TblOrders
    WHERE FldInstrumentID = instrument AND FldLastTradePrice != 0 
      AND FldLastTradeQuantity != 0
) a;

Upvotes: 1

Related Questions