1
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

0

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
  • To clarify, I mean for og.NumShipped or ig.NumReceived, OnHand works as intended but I intend to retrieve and display og.NumShipped and ig.NumReceived individually. – Dan185 Aug 5 '18 at 17:44
0

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://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql

0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.