Access sql statement - agregate function

yjchong514
yjchong514 used Ask the Experts™
on
Example:
 Quantity           Stock Type      
       5                  IN
     10                  IN
       3                  OUT
       7                  IN

Result
Balance of all stock = Sum of IN type - Sum of Out type
E.g: Sum of IN type = 22
       Sum of OUT type = 3
       Balance of all stock = 22-3
                                       = 17
I WANT THE QUERY RESULT RETURN 17

Note: When there is only STOCK IN, the query should return balance for STOCK IN .
         When there is only STOCK OUT, the query should return balance for STOCK OUT.
         When there is no STOCK, the query should return nil balance.

Remember the query result is only one output, which is the current stock balance. But it caters for
conditions stated in Note above.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
How does one get 17 by subtracting three from 22?

Commented:
try this...


SELECT	CASE WHEN (InQnt=0 AND OutQnt=0) THEN NULL
				WHEN (InQnt=0 AND OutQnt<>0) THEN OutQnt
				WHEN (InQnt<>0 AND OutQnt=0) THEN InQnt
				WHEN (InQnt<>0 AND OutQnt<>0) THEN InQnt-OutQnt END AS Balance
FROM	(
		SELECT	ISNULL((SELECT SUM(Quantity) FROM YourTable t1 WHERE Stock='IN'),0) AS InQnt
				, ISNULL((SELECT SUM(Quantity) FROM YourTable t1 WHERE Stock='OUT'),0) AS OutQnt) t

Open in new window

Top Expert 2010
Commented:
SELECT Sum(IIf([Stock Type] = "IN", Quantity, -Quantity)) AS NetQuantity
FROM [SomeTable]

Author

Commented:
22-3=19

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial