DiH DiH - 2 months ago 8
SQL Question

SQL Server 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 I
SUM
if one of them is Null? Should I use
CASE
or something?

Answer

use

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