Results 1 to 7 of 7

Thread: Sum Query

  1. #1
    Join Date
    Jan 2004
    Posts
    17

    Unhappy Unanswered: Sum Query

    I'm using two tables sales and purchase.

    to get the current stock i'm using the query...

    SELECT Nz(Sum(Purchase.QTY),0) - Nz(Sum(Sales.QTY),0) AS Stock
    FROM Purchase, Sales;

    It gives the perfect value when both the tables has some values, but fails to get the value when any of the tables Qty returns nothing.

    i.e nothing - 10(some value) = nothing

    I'm also converting null to zero using Nz Function but then also the problem is not solved. (it should give me -10)

  2. #2
    Join Date
    Jan 2004
    Location
    Romania - Bucharest
    Posts
    50
    try replace NZ with ISNULL ( Field, 0 )

    See Books Online for details on ISNULL()

  3. #3
    Join Date
    Jan 2004
    Posts
    17

    does not solve the purpose

    Select Isnull(Sum(Purchase.QTY)) - isNull(SUM(Salses.QTY)) as stock from Purchase,Sales

    Purcahse table is empty and sales table has 10 qty, it should return -10 but instead its returning 0.

    plz help

  4. #4
    Join Date
    Jan 2004
    Location
    Romania - Bucharest
    Posts
    50
    Sorry, I had the impresion you were working in SQL Server

    Try this:

    SELECT Sum(NZ(Purchase.QTY, 0)) - Sum(NZ(Sales.QTY,0)) AS Stock
    FROM Purchase, Sales;

  5. #5
    Join Date
    Jan 2004
    Posts
    17

    Red face Not working

    It still gives no value, it should give me -10.

    I'm stuck on this problem...

  6. #6
    Join Date
    Jan 2004
    Location
    Romania - Bucharest
    Posts
    50
    Ok, then the problem is in the jointype you are using. I assume you are using an inner join (btw, try allways using the construct "from table1 inner join table2" instead "from table1, table2"), that is you have some data returned only when you have corresponding data in both tables.

    Try using either "left outer join" or "right outer join". That shoild do the trick...

  7. #7
    Join Date
    Jan 2004
    Posts
    17

    Red face help

    SELECT Nz(Sum(Purchase.QTY),0) - Nz(Sum(Sales.QTY),0) AS Stock
    FROM Purchase, Sales;

    even tried joins but not getting results.....

    plz format a query for this vierd problem.

Posting Permissions

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