DiH DiH - 2 months ago 7
SQL Question

Sql sum and subtract prevent null results

I'm using this code:

SELECT
(
SELECT SUM(TrnQty) AS Total
FROM InventoryTrans
WHERE InventoryItemID =
(
select MAX (InventoryItemID) from inventorymaster
)
and CustomerID='0'
) -
(
SELECT SUM(TrnQty) AS Total
FROM InventoryTrans
WHERE InventoryItemID =
(
select MAX (InventoryItemID) from InventoryMaster
)
and CustomerID > '0'
)


If the result of one them is NULL i take totally result NULL. How can Sum if one of them is Null?Should I use Case or something?

Answer

use

SELECT coalesce(SUM(TrnQty), 0) ...