Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: subtract fields in the same table

    hi everyone,
    i just cant figure out how to subtract fields in the same table.
    Code:
    SELECT
    SUM( a.amountfigure ) AS 'Dollars'
    , 'Buying' AS 'Transact'
    FROM forex_tbl a
    WHERE a.currency = 'USD' AND a.transactiontype = 'Buying'
    
    UNION ALL
    
    SELECT
    SUM( b.amountfigure ) AS 'Dollars'
    , 'Selling' AS 'Transact'
    FROM forex_tbl b
    WHERE b.currency = 'USD' AND b.transactiontype = 'Selling';
    i want the 'Dollars' in buying be subtract to 'Dollars' in selling.
    how can i do this.

    thanks

    ok i already get it...
    Code:
    SELECT
    (SELECT
    SUM( a.amountfigure ) AS 'Dollars'
    FROM forex_tbl a
    WHERE a.currency = 'USD' AND a.transactiontype = 'Buying') AS 'Buying'
    
    ,(SELECT
    SUM( b.amountfigure ) AS 'Dollars'
    FROM forex_tbl b
    WHERE b.currency = 'USD' AND b.transactiontype = 'Selling') AS 'Selling';
    whew....
    any other solutions guys?

    thanks
    Last edited by homer.favenir; 01-22-09 at 02:22.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This any good?
    Code:
    SELECT Sum(CASE WHEN transactiontype = 'Buying'  THEN amountfigure END) As [Buying]
         , Sum(CASE WHEN transactiontype = 'Selling' THEN amountfigure END) As [Selling]
    FROM   forex_tbl
    WHERE  currency = 'USD'
    
    SELECT Sum(CASE WHEN transactiontype = 'Buying'  THEN amountfigure END)
         - Sum(CASE WHEN transactiontype = 'Selling' THEN amountfigure END) As [Dollars]
    FROM   forex_tbl
    WHERE  currency = 'USD'
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •