Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    5

    Unanswered: sql query to substract to columns

    I have the following sql statement in Data environment that currently gives me this report. I am using vb6sp6 and access 2002 db.
    HTML Code:
    SPACENO| NAME |TOTALDUE| CHECKAMT| DIFFERENCE
    10        Joe        300       100            200
    20        Mary       300      300              0
    30        Dave       300             
    40        Tom        300      300              0
                                              total =  200
    basically, total due - checkamt will equal diff., if there are duplicate spaceno
    then the two checkamt will add up and only show one spaceno. The the total of all diff is calculated at the end of the report. I dont need any sql for the total.
    Code:
    SELECT gas_bill.spaceno, max(gas_bill.name_gas) as name_gas, Max(gas_bill.total_due) as total_due, Sum(receipts.check_amt) as check_amt,
    (Max(gas_bill.total_due) -Sum(receipts.check_amt)) as diffl
    FROM receipts RIGHT JOIN gas_bill ON receipts.spaceno=gas_bill.spaceno group by gas_bill.spaceno
    I need to add another column called cashamt to do the same as check amt to have the same function and using the same diff column to show the results.
    HTML Code:
    SPACENO| NAME |TOTALDUE| CHECKAMT| CASHAMT | DIFF 
    10        Joe     300       100           200
    20        Mary   300       300           0
    30        Dave   300            
    40        Tom    300       300           0
    50        AL      300                300  0
                                                        total =  200
    Last edited by obulldog27; 09-23-05 at 19:04.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Could it be that you need to change "MAX(total_due)" to "SUM(total_due)" (twice) in
    Code:
    SELECT gas_bill.spaceno, max(gas_bill.name_gas) as name_gas, MAX(gas_bill.total_due) as total_due, Sum(receipts.check_amt) as check_amt,
    (MAX(gas_bill.total_due) -Sum(receipts.check_amt)) as diffl
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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