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 > ''
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden