select *,
(select COUNT(1) from WMS_Package where ShipmentId=a.Id and Status='10') as WaitingShippedLoadScan,
(select COUNT(1) from WMS_Package where ShipmentId=a.Id and Status='20') as ShippedLoadScan
from WMS_Shipment a where a.Id in (select ShipmentId from WMS_Package where Status='10' or Status='20' group by ShipmentId)