一种方法使用窗口函数：

select item,quantity,cnt
from (select item,count(*) as cnt,row_number() over (partition by item order by count(*) desc) as seqnum
      from t
      group by item,quantity
     ) t
where seqnum = 1;

您可以结合使用 count 聚合函数和 row_number 解析函数来使用此解决方案。

select ITEM,QUANTITY,CNT
from (
select Item,Quantity,count(*)cnt,row_number()over(partition  by Item order by count(*) desc)rnb
from t
group by Item,Quantity
)
where RNB = 1
order by ITEM
;

您要提取的内容在统计中称为模式。 Oracle 为此内置了聚合函数：stats_mode。但是，我不知道该函数，它可能会在一次传递中返回模式计数。内置功能和以下解决方案不考虑关系。如果没有关系的子查询，我无法想出解决方案

最简单的方法是对解析函数执行 distinct 以获得所需的结果，而无需子查询：

select distinct
  item,max(quantity)
    keep(dense_rank first
    order by count(*) desc
  ) over(partition by item)
    as quantity_mode,max(count(*))
    over(partition by item)
    as mode_count
from t
group by item,quantity

ITEM       | QUANTITY_MODE | MODE_COUNT
:--------- | ------------: | ---------:
Crankshaft |           200 |          3
Housing    |            50 |          2
Gear       |           400 |          2

或者对聚合的所有值进行一些技巧，例如在集合变量中或通过 xmlagg 和 XQuery 计算此值。根据您想要避免子查询的原因，无论您想要什么。

一种可能的方法是计算 listagg 结果中出现的次数。下面我使用百分号作为双方的分隔符，以允许对精确标记进行计数，而不管它们的相对位置如何（当一个分隔符需要在后续项目中计算两次时，不解决问题）。为避免 varchar2 限制，您可以在 12c+ 中使用 max_string_size = extended 或将相同的逻辑应用于 xmlagg(...).getclobval() 结果并适当更改正则表达式。

select
  item,stats_mode(quantity) as quantity_mode,regexp_count(
    listagg (
      '%' || to_char(quantity,'TM9') || '%')
      within group (order by 1),'%' ||
      to_char(stats_mode(quantity),'TM9')
      || '%'
  ) as mode_cnt
from t
group by item

ITEM       | QUANTITY_MODE | MODE_CNT
:--------- | ------------: | -------:
Crankshaft |           200 |        3
Gear       |           400 |        2
Housing    |            50 |        2

dbfiddle here