PLSQL: Summing not exists?-CodePudding

I've searched high and low for a resolution to my problem and attempted multiple things.

What the query is trying to do is sum values from table "qo" and table "qoh" and group by "SKU". It will then subtract one from another to give me a value that I'm "short".

My issue:

If there are no values in Table "qoh" the SKU doesn't exist, not even as a null value! Where i'd expect the Total value from table "qo" to remain the same because there is nothing to deduct, what is actually happening is it removes the row from table "qo" altogether and returns nothing.

So basically, if it doesn't exist in qoh then nothing is returned even knowing there's a value in qo.

Can anyone see where i'm going wrong here?

Thanks

    SELECT qo.sku_id
, SUM(qo.QTY_ORDERED_II) AS "Total ordered"
, SUM(qoh.total_inventory) AS "Total Inventory"
, ABS(SUM(QTY_ORDERED_II - TOTAL_INVENTORY)) AS "Short"

FROM

    (SELECT SKU_ID, SUM(qty_ordered) AS QTY_ORDERED_II
     FROM order_line
     GROUP BY order_line.sku_id) 
     
     qo
     
 JOIN 
    
    (SELECT sku_id, SUM(qty_on_hand) AS TOTAL_INVENTORY 
     FROM INVENTORY 
     GROUP BY sku_id) 
     
     qoh
                                                                                            
ON qo.sku_id = qoh.sku_id
GROUP BY qo.sku_id
HAVING SUM(QTY_ORDERED_II - TOTAL_INVENTORY) > 0 
ORDER BY SKU_ID;

CodePudding user response：

Here's the SQL, just in case the comments aren't sufficient.

Note the HAVING clause change too.

SELECT qo.sku_id
     , SUM(qo.QTY_ORDERED_II) AS "Total ordered"
     , SUM(qoh.total_inventory) AS "Total Inventory"
     , ABS(SUM(QTY_ORDERED_II - COALESCE(TOTAL_INVENTORY, 0))) AS "Short"
  FROM
  (SELECT SKU_ID, SUM(qty_ordered) AS QTY_ORDERED_II
     FROM order_line
     GROUP BY order_line.sku_id) 
        qo
     
 LEFT JOIN 
    
    (SELECT sku_id, SUM(qty_on_hand) AS TOTAL_INVENTORY 
     FROM INVENTORY 
     GROUP BY sku_id) 
     
     qoh
                                                                                            
    ON qo.sku_id = qoh.sku_id
 GROUP BY qo.sku_id
HAVING SUM(QTY_ORDERED_II - COALESCE(TOTAL_INVENTORY, 0)) > 0 
 ORDER BY SKU_ID;