Returning 0 if value is NULL

Question

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

Answer 1

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

Answer 2

For more complex conditional expressions, use a CASE expression: https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case

The documentation for MySQL describes CASE expressions as part of the control flow topic, since the same CASE keyword is also used for conditional statement execution. The documentation is overly concise, and in the wrong place IMHO :-) CASE expressions are extremely useful feature.

SQL Server's documentation for CASE expressions is more elaborate, and the syntax is the same as MySQL: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql

Answer 3

It was

SELECT
    p.id
  , coalesce(ig.NumReceived, 0) as NumReceived
  , coalesce(og.NumShipped, 0) as 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;