Returning 0 if value is NULL 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
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