# Thread: Sum Query

1. Registered User
Join Date
Jan 2004
Posts
17

## 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. Registered User
Join Date
Jan 2004
Location
Romania - Bucharest
Posts
50
try replace NZ with ISNULL ( Field, 0 )

See Books Online for details on ISNULL()

3. Registered User
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. Registered User
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. Registered User
Join Date
Jan 2004
Posts
17

## Not working

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

I'm stuck on this problem...

6. Registered User
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. Registered User
Join Date
Jan 2004
Posts
17

## 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
•