您不需要分析函数，可以使用条件聚合-正常聚合，但要使用case表达式（条件部分）来确定要包含在计数中的行：

with tree_row
as (
select '1111' as tree_id, 'PULP' as tree_product from dual union all
select '1111' as tree_id, 'PULP' as tree_product from dual union all
select '2222' as tree_id, 'PULP' as tree_product from dual union all
select '2222' as tree_id, 'CHIP' as tree_product from dual union all
select '3333' as tree_id, 'PULP' as tree_product from dual union all
select '3333' as tree_id, 'CHIP' as tree_product from dual union all
select '3333' as tree_id, 'CHIP' as tree_product from dual
)
select tree_id, 
       count(case when tree_product = 'PULP' then tree_id end) as pulp_count,
       count(case when tree_product = 'CHIP' then tree_id end) as chip_count,
       count(*)                                                as tree_total
       from tree_row
       group by tree_id;

| 树ID|纸浆计数|芯片计数|树_总计|
| - ------|- ------|- ------|- ------|
| 小行星|第二章|无|第二章|
| 小行星2222|1个|1个|第二章|
| 小行星3333|1个|第二章|三个|
fiddle