How to use ISNULL result in a calculation involving a subquery

Question

I have a SQL script that uses a subquery, the result of the subquery can sometimes be NULL so I've used ISNULL to set the result to zero (0) in those cases.

The problem I have to solve is that I need to use that zero in a calculation. The calculation is to see if the system has enough inventory of a specific item, so its basically: Quantity In Stock - Quantity Needed = Balance

If the subquery returns 0 and qty. 8 is needed then the result should be -8 but currently the result will just show as 0 because of the ISNULL function.

ISNULL((
    SELECT
        SUM(ILE.[Remaining Quantity]) - CTE.[ExtendedQuantity] 
    FROM [Item Ledger Entry] ILE
    GROUP BY ILE.[Item No_]
    HAVING (CTE.[ItemNo] = ILE.[Item No_])
),0) AS [Balance]

I tried to use a case expression but wasn't exactly sure how to structure that with the subquery to make it work, it still may be possible.

Current Result:

Item No.	Qty. In Stock	Qty. Needed	Balance
017030	0	8	0

Desired Result:

Item No.	Qty. In Stock	Qty. Needed	Balance
017030	0	8	-8

I think I need to modify how I'm using the ISNULL in some way just not sure what approach to try, please let me know if anything more is needed.

Answer 1

isnull() should only applied to your subquery

ISNULL((
    SELECT 
        SUM(ILE.[Remaining Quantity]) 
    FROM  
        [Item Ledger Entry] ILE
    WHERE 
        CTE.[ItemNo] = ILE.[Item No_]
),0) - CTE.[ExtendedQuantity]  AS [Balance]

Answer 2

If the row is empty than you can wrap this around ISNULL.

   ISNULL((SELECT
        SUM( ISNULL(ILE.[Remaining Quantity],0)) - CTE.[ExtendedQuantity] 
    FROM [Item Ledger Entry] ILE
    GROUP BY ILE.[Item No_]
    HAVING (CTE.[ItemNo] = ILE.[Item No_])
),0) AS [Balance]