SQL - Sum then divide by zero problems

Question

I'm attempting to sum 2 columns and divide one by the other however I'm facing problems when dividing by zero.

The maths is essentially Sum(a)/Sum(b)

My code:

SELECT
      SUM(Inventory.QuantityOutstanding) as Quantity,   

      SUM(inventory.TotalValueOutstanding) as ExtValue,

      (SELECT NULLIF(sum(Inventory.TotalValueOutstanding), 0) /
      NULLIF(SUM(Inventory.QuantityOutstanding), 0)  FROM Inventory)  as AcctValue

FROM Inventory 
GROUP BY inventory.TotalValueOutstanding 

My expected result:

| Quantity | Extvalue  | AcctValue
|  -10.00  | -92.00    |  0.11
|   13.00  | 0.00      |  0
|   0.00   | 0.00      |  0
|   2.00   | 16.00     |  0.125

My actual output:

| Quantity | Extvalue  | AcctValue
|  -10.00  | -92.00    |  76.603066544004906470
|   13.00  | 0.00      |  76.603066544004906470
|   0.00   | 0.00      |  76.603066544004906470
|   2.00   | 16.00     |  76.603066544004906470

Answer 1

I'm not sure how these are being grouped, but I would assume that it is some kind of item or ID? For the sake of argument, I've called that ColumnID, but you can replace it with what you are actually using (e.g. InventoryItemName?)

You can then try this, which just checks if quantity outstanding is 0. If it is then the answer is just 0, else calculate the formula as normal.

SELECT
   Inventory.ColumnID,
   SUM(Inventory.QuantityOutstanding) as Quantity,   

   SUM(inventory.TotalValueOutstanding) as ExtValue,

   IIF(SUM(Inventory.QuantityOutstanding) = 0
        ,0
        ,isnull(sum(Inventory.TotalValueOutstanding), 0) /
           sum(Inventory.QuantityOutstanding)
       ) as AcctValue
FROM 
   Inventory 
GROUP BY
   Inventory.ColumnID