SELECT
  ztb_Carrier_Delivery_Metrics.Delivery,
  ztb_Carrier_Delivery_Metrics.Delivery_Item,
  ztb_IMP_Notifications.Sales_Order_Number,
  ztb_Carrier_Delivery_Metrics.REF_DOC,
  -- insert Notification_cnt here
  MyAlias.Notification_cnt
FROM            ztb_Carrier_Delivery_Metrics
INNER JOIN      ztb_IMP_Notifications
  ON ztb_Carrier_Delivery_Metrics.REF_DOC = ztb_IMP_Notifications.Sales_Order_Number
LEFT OUTER JOIN (SELECT
                   sales_order_number,
                   COUNT(sales_order_number) AS Notification_cnt
                 FROM   ztb_IMP_Notifications
                 GROUP  BY sales_order_number
                 ) MyAlias
  ON ztb_IMP_Notifications.sales_order_number = MyAlias.sales_order_number
WHERE           ztb_carrier_Delivery_Metrics.Ref_Doc > '' 

WITH Notifications AS (
    SELECT sales_order_number
        , count(sales_order_number) as Notification_cnt 
    FROM ztb_IMP_Notifications
    GROUP BY sales_order_number)
SELECT ztb_Carrier_Delivery_Metrics.Delivery
    , ztb_Carrier_Delivery_Metrics.Delivery_Item
    , n.Sales_Order_Number
    , ztb_Carrier_Delivery_Metrics.REF_DOC
    , n.Notification_cnt
FROM ztb_Carrier_Delivery_Metrics cdm
INNER JOIN Notifications n ON cdm.REF_DOC = n.Sales_Order_Number
WHERE cdm.Ref_Doc > '' 

SELECT 
	d.Delivery,
	d.Delivery_Item,
	n.Sales_Order_Number,
	d.REF_DOC,
	n.Notification_cnt
FROM ztb_Carrier_Delivery_Metrics d 
INNER JOIN (
	SELECT 
		sales_order_number,
		Notification_cnt = COUNT(sales_order_number) 
	FROM ztb_IMP_Notifications
	GROUP BY sales_order_number
) n 
	ON n.Sales_Order_Number = d.REF_DOC 
WHERE d.Ref_Doc > ''

SELECT 
	d.Delivery,
	d.Delivery_Item,
	--n.Sales_Order_Number,
	d.REF_DOC--,
	--n.Notification_cnt
FROM ztb_Carrier_Delivery_Metrics d 
WHERE d.REF_DOC = '0002619226'