CODEWITHSUNDEEP
mysqlsql
Christian
Christian

Reputation: 3972

MySQL sum up two opposite currency pairs

I have the following buy and sell currency pairs and their values in a table and im trying to write a small select statement that gets the total of each pair, but when the pairs have opposites, they can deduct from each other, for example, there is no point selling any EUR because it can be used to cancel out some of the buying EUR. So the two lines GBP/EUR, EUR/GBP would become: Note the pairs may not only match on EUR/GBP so can't be hard coded in

EUR 852.07  GBP -636.1

(maybe focusing on reducing the amount to buy is the way to go?)

I would love to give you an example but I am completely stuck! Any ideas are greatly appreciated. 

Buy             Sell
ccy  amt        ccy  amt
EUR, 1409.7600, AUD, -1965.4900
GBP, 2801.8500, AUD, -5446.5100
EUR, 1116.2800, CAD, -1472.1500
EUR, 4862.2000, CHF, -5013.9000
GBP, 3937.6700, CHF, -5661.5700
EUR, 2066.8900, DKK, -15505.0000
GBP, 1688.7200, DKK, -17640.6400
**
GBP, 1986.4400, EUR, -2778.7500
EUR, 3630.8200, GBP, -2622.5400
**
EUR, 358.4100,  NOK, -3046.6600
GBP, 2865.9800, NOK, -33965.5900
EUR, 1574.9800, SEK, -14746.0600
GBP, 1511.3100, SEK, -19727.5900
EUR, 724.3600,  USD, -777.8900
GBP, 34.7400,   USD, -52.0200


ccy = currency char(3)
amt = amount decimal

The final result then should look like this.

Buy             Sell
ccy  amt        ccy  amt
EUR, 1409.7600, AUD, -1965.4900
GBP, 2801.8500, AUD, -5446.5100
EUR, 1116.2800, CAD, -1472.1500
EUR, 4862.2000, CHF, -5013.9000
GBP, 3937.6700, CHF, -5661.5700
EUR, 2066.8900, DKK, -15505.0000
GBP, 1688.7200, DKK, -17640.6400
**
EUR, 852.07     GBP, -636.1
**
EUR, 358.4100,  NOK, -3046.6600
GBP, 2865.9800, NOK, -33965.5900
EUR, 1574.9800, SEK, -14746.0600
GBP, 1511.3100, SEK, -19727.5900
EUR, 724.3600,  USD, -777.8900
GBP, 34.7400,   USD, -52.0200

Upvotes: 1

Views: 406

Answers (4)

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/d5198/1

SELECT  
    IF(buy_ccy>sell_ccy,buy_ccy,sell_ccy) as buy_ccy,
    SUM(IF(buy_ccy>sell_ccy,buy_amt,sell_amt)) as buy_amt,
    IF(buy_ccy>sell_ccy,sell_ccy,buy_ccy) as sell_ccy,
    SUM(IF(buy_ccy>sell_ccy,sell_amt,buy_amt)) as sell_amt
FROM EXCHANGE
GROUP BY IF(buy_ccy>sell_ccy,CONCAT(buy_ccy,sell_ccy),CONCAT(sell_ccy,buy_ccy))

Upvotes: 0

trex005
trex005

Reputation: 5115

I had to slightly change your column names to reflect the fact that there were multiple ccy and amt in the same record.

SELECT
a.buy_ccy,
SUM(a.buy_amt) + COALESCE(SUM(b.sell_amt),0) as buy_amt,
a.sell_ccy,
SUM(a.sell_amt) + COALESCE(SUM(b.buy_amt),0) as sell_amt
FROM
transactions as a
LEFT OUTER JOIN transactions as b ON (a.buy_ccy = b.sell_ccy AND a.sell_ccy = b.buy_ccy)
GROUP BY
a.buy_ccy,
b.buy_ccy,
a.sell_ccy,
b.sell_ccy
HAVING buy_amt > 0;

Example: http://sqlfiddle.com/#!9/f2006/3

Upvotes: 2

Juan
Juan

Reputation: 1426

Try this:

    SELECT SUM( CASE 
          WHEN `ccy_buy` = 'EUR' AND `ccy_sell` = 'GBP' THEN `amt_buy`
          WHEN `ccy_sell` = 'EUR' AND `ccy_buy` = 'GBP' THEN `amt_sell`
       END
      ) AS `ccy1`,
      SUM( CASE 
          WHEN `ccy_buy` = 'GBP' and `ccy_sell` = 'EUR' THEN `amt_buy`
          WHEN `ccy_sell` = 'GBP' and `ccy_buy` = 'EUR' THEN `amt_sell`
       END
      ) AS `ccy2`
    FROM `test`

You may want to wrap it into a stored procedure and pass the two currencies as parameters, something like this:

DELIMITER ;;
CREATE PROCEDURE `sp_compareCurrencyPairs`(IN pCur1 CHAR(3), IN pCur2 CHAR(3))
    READS SQL DATA
BEGIN
    SELECT SUM( CASE 
          WHEN `ccy_buy` = pCur1 AND `ccy_sell` = pCur2 THEN `amt_buy`
          WHEN `ccy_sell` = pCur1 AND `ccy_buy` = pCur2 THEN `amt_sell`
       END
      ) AS `ccy1`,
      SUM( CASE 
          WHEN `ccy_buy` = pCur2 AND `ccy_sell` = pCur1 THEN `amt_buy`
          WHEN `ccy_sell` = pCur2 AND `ccy_buy` = pCur1 THEN `amt_sell`
       END
      ) AS `ccy2`
    FROM `test`;
END;;
DELIMITER ;

Then use it like this:

CALL `sp_compareCurrencyPairs`('GBP', 'EUR');

Upvotes: 1

dbinns66
dbinns66

Reputation: 791

Try something like this and assuming you're using PHP...

$sql = "
select
  sum( 
    case 
      when buy_currency = '$currency_one' then buy_amount
      when sell_currency = '$currency_one' then sell_amount
   end
  ) as first_currency,
  sum( 
    case 
      when buy_currency = '$currency_twp' then buy_amount
      when sell_currency = '$currency_two' then sell_amount
   end
  ) as second_currency
from test";

assuming schema like 

CREATE TABLE `test` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `buy_currency` VARCHAR(50) NOT NULL DEFAULT '0',
    `buy_amount` VARCHAR(50) NOT NULL DEFAULT '0',
    `sell_currency` VARCHAR(50) NOT NULL DEFAULT '0',
    `sell_amount` VARCHAR(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=6
;

Upvotes: 0

Related Questions