MySQL COALESCE – Returning 0 if Value is NULL

coalesceMySQLmysql-5.7null

SELECT
    p.id
  , ig.NumReceived
  , og.NumShipped
  , coalesce(
        (p.StartingInventory - og.NumShipped + ig.NumReceived),
        p.StartingInventory
      ) as OnHand
  , p.ProductName
  , p.StartingInventory
  , p.MinimumRequired
FROM
  products p
        left outer join (
            select productid, sum(NumReceived) as NumReceived
            from incoming
            group by productid
        ) as ig on p.id = ig.productid
        left outer join (
            select productid, sum(NumberShipped) as NumShipped
            from outgoing
            group by productid
        ) as og on p.id = og.productid

I managed to get coalesce() to function correctly in this statement for (p.StartingInventory-og.NumShipped+ig.NumReceived) but my intention is to return 0 if ig.NumReceived or og.NumShipped return 0 as well. By which I mean, retrieving NumShipped or NumReceived will display 0. coalesce((p.StartingInventory-og.NumShipped+ig.NumReceived) works as it should but the line i'm referring to is ig.NumReceived, og.NumShipped

I've also tried IFNULL(). I've tried encompassing just the ig.NumReceived as well as the select statement. I'm using MySql 5.7.22

Best Answer

my intention is to return 0 if ig.NumReceived or og.NumShipped return 0 as well

If so, convert

coalesce((p.StartingInventory-og.NumShipped+ig.NumReceived), p.StartingInventory) as OnHand

to, for example,

coalesce(  p.StartingInventory - og.NumShipped + ig.NumReceived,
           case when ig.NumReceived = 0 or og.NumShipped = 0 
                then 0 
                else p.StartingInventory 
                end               
        ) as OnHand
Related Question