Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE STOCKS(STOCKID, NAME) AS
  SELECT 1, 'NUTS' FROM DUAL UNION ALL
  SELECT 2, 'BOLTS' FROM DUAL UNION ALL
  SELECT 3, 'TOMATOES' FROM DUAL UNION ALL
  SELECT 4, 'AR-15' FROM DUAL;
4 rows affected

CREATE TABLE ORDERS(ORDERID, STOCKID) AS
  SELECT 1, 1 FROM DUAL UNION ALL
  SELECT 1, 3 FROM DUAL UNION ALL
  SELECT 2, 2 FROM DUAL UNION ALL
  SELECT 2, 4 FROM DUAL UNION ALL
  SELECT 3, 3 FROM DUAL UNION ALL
  SELECT 4, 1 FROM DUAL UNION ALL
  SELECT 4, 2 FROM DUAL UNION ALL
  SELECT 4, 3 FROM DUAL UNION ALL
  SELECT 4, 4 FROM DUAL;
9 rows affected

select count(Orders.OrderID)
  from Stocks, Orders
  where Stocks.StockID = Orders.StockID(+)
  group by Stocks.StockID
  ORDER BY STOCKS.STOCKID


COUNT(ORDERS.ORDERID)


2

2

3

2


SELECT DISTINCT s.NAME,
       COUNT(*) OVER (PARTITION BY s.STOCKID ORDER BY s.STOCKID) AS ORDERS_FOR_ITEM
  FROM STOCKS s
  LEFT OUTER JOIN ORDERS o
    ON s.STOCKID = o.STOCKID


NAME
ORDERS_FOR_ITEM


NUTS
2

TOMATOES
3

BOLTS
2

AR-15
2