Предложение WITH: Включает только определенные значения из каждой таблицы

Вы можете попробовать это:

WITH 
    C0R_Count AS (
        SELECT /*+ materialize */ 
        sku_id, count(location_id) as Bulk_Count
        FROM Inventory
        where location_id like 'C0R%'
        group by sku_id),
    C0S_Count AS (
        SELECT /*+ materialize */ 
        sku_id, count(location_id) as Bin_Count
        FROM Inventory
        where location_id like 'C0S%'
        group by sku_id)
SELECT RANK() OVER (ORDER BY nvl(Bin_Count,0)+nvl(Bulk_Count,0) DESC)as Count_Rank,
       Inventory.sku_id, Bin_Count, Bulk_Count, 
       (nvl(Bin_Count,0)+nvl(Bulk_Count,0)) as Total_Count
FROM Inventory
Left Join C0R_Count
   on Inventory.Sku_id = C0R_Count.Sku_id
Left Join C0S_Count
   on Inventory.Sku_id = C0S_Count.Sku_id
WHERE 
   client_id = 'SDRY-US' 
   and site_id = 'USCOL1' 
   and (nvl(Bin_Count,0)+nvl(Bulk_Count,0)) > 9
GROUP BY Inventory.sku_id, Bin_Count, Bulk_Count
;

Когда вычисляются нулевые значения, они становятся нулевыми.

Ваш запрос выглядит намного сложнее, чем нужно. Вы можете сделать все это с условным агрегированием и без подзапросов или CTE:

select rank() over (order by sum(case when location_id like 'C0R%' or location_id like 'C0s%' then 1 else 0 end) desc) as count_rank,
       sku_id,
       sum(case when location_id like 'C0R%' then 1 else 0 end) as Bulk_Count
       sum(case when location_id like 'C0S%' then 1 else 0 end) as Bin_Count
from Inventory i
where client_id = 'SDRY-US' and site_id = 'USCOL1' 
group by sku_id
having sum(case when location_id like 'C0R%' or location_id like 'C0s%' then 1 else 0 end) > 9;

Вы можете ввести подзапрос просто для упрощения псевдонимов столбцов:

select rank() over (order by Bulk_Count + Bin_Count desc) as count_rank,
       i.*
from (select sku_id,
             sum(case when location_id like 'C0R%' then 1 else 0 end) as Bulk_Count
             sum(case when location_id like 'C0S%' then 1 else 0 end) as Bin_Count
      from Inventory i
      where client_id = 'SDRY-US' and site_id = 'USCOL1' 
      group by sku_id
     ) i
where Bulk_Count + Bin_Count > 9

Я продолжу и опубликую это как ответ - вам нужно использовать здесь синтаксис LEFT/RIGHT JOIN. СУБД будет логически интерпретировать ваш существующий SQL, чтобы неявно означать INNER объединений.

Кроме того, вам, возможно, придется пересмотреть ваши GROUP BY пункты.

Возможно, вам потребуется использовать подзапросов - сначала выберите интересующие строки, а затем суммируйте их.