Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    New Zealand
    Posts
    1

    Unanswered: Calculating with Null values

    Gone right back to first principles on this one.
    I have set up two tables "Bank" with 2 fields Account and Balance. Account is primary key which is relationshipped to table 2 "Sales" which also has 2 fields Account and Sales.
    Have put some figures in including some "$0.00" for Bank and in Sales have only entered a few.

    I am trying to make a query list ALL bank accounts and add any Sales that refer to the same account.
    I know it involves Null values and have tried every permutation I can think of but the closest I have got was a result where each account concatinated each entry in the sales figures ignoring the account.

    I tried Nz'ing but must be in the wrong place.

    Where are things supposed to go and exactly how are they supposed to be added to show All including zero results.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select b.account
         , b.balance + iif(isnull(s.balance), 0, s.balance) 
      from bank b
    left outer
      join sales s
        on b.account = s.account
    rudy
    http://r937.com

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Rudy: The bank balance may be NULL, too, and you are getting a record per Sales, and not per Bank. This query should do the job:

    SELECT Bank.Account,
    Min(IIf(IsNull([bank].[Balance]),0,[bank].[Balance])) AS [Bank Balance],
    Sum(IIf(IsNull([Sales].[Balance]),0,[Sales].[Balance])) AS [Sales Balance]
    FROM Bank LEFT JOIN Sales ON Bank.Account = Sales.Account
    GROUP BY Bank.Account;
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good point about summing the sales to get one record, that was a real mistake on my part (i was tired last night)

    but the bank balance would not be null -- there's no way

    how could you have an account with an unknown balance?

    shoot the application programmer!!!

    the IIF(ISNUL()) is only to guard against the unmatched rows in the left outer join


    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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